Excel Curve Fit Coefficients 5

[rnordquest]

How do I access the curve fit coefficients? For example, I want to curve fit a 2nd order polynomial to a set of data and display the coefficients in separate cells then evaluate the curve fit at a defined X value (say cell B5); i.e. y = A5*B5^2 + A6*B5 + A7. So how do I extract the coefficients without copy and paste and get them in column A?

Next time around I drag and drop in new data and my new Y value is immediately calculated. And I want to do this without a macro. Hopefully I was able to sufficiently describe this.

 

[MintJulep]

See the LOGEST() function in excel help.

 

[Clyde38]

Check thread770-179377

These equations assume that your sheet has two named ranges: x and y.

Linear Trendline
Equation: y = m * x + b
m: =SLOPE(y,x)
b: =INTERCEPT(y,x)

Logarithmic Trendline
Equation: y = (c * LN(x)) - b
c: =INDEX(LINEST(y,LN(x)),1)
b: =INDEX(LINEST(y,LN(x)),1,2)

Power Trendline
Equation: y=c*x^b
c: =EXP(INDEX(LINEST(LN,LN(x),,),1,2))
b: =INDEX(LINEST(LN,LN(x),,),1)

Exponential Trendline
Equation: y = c *e ^(b * x)
c: =EXP(INDEX(LINEST(LN,x),1,2))
b: =INDEX(LINEST(LN,x),1)

2nd Order Polynomial Trendline
Equation: y = (c2 * x^2) + (c1 * x ^1) + b
c2: =INDEX(LINEST(y,x^{1,2}),1)
C1: =INDEX(LINEST(y,x^{1,2}),1,2)
b = =INDEX(LINEST(y,x^{1,2}),1,3)

3rd Order Polynomial Trendline
Equation: y = (c3 * x^3) + (c2 * x^2) + (c1 * x^1) + b
c3: =INDEX(LINEST(y,x^{1,2,3}),1)
c2: =INDEX(LINEST(y,x^{1,2,3}),1,2)
C1: =INDEX(LINEST(y,x^{1,2,3}),1,3)
b: =INDEX(LINEST(y,x^{1,2,3}),1,4)

Higher Order Polynomial Trendline
Notice the pattern in the two preceding sets of formulas

 

[joerd]

Great method.

You can obtain some statistical analysis data as well, to see how good your fit is. For example, the R[sup]2[/sup] value in case of a 2nd order polynomial:
[tt]r^2: =INDEX(LINEST(y,x^{1,2},TRUE,TRUE),3)[/tt]
or in case of a log trendline:
[tt]r2: =INDEX(LINEST(y,LN(x),TRUE,TRUE),3)[/tt]

If you have a more complex function, one approach is to estimate the coefficients, and compute "fitted" y-values to each x value, then compute the sum of squared errors. You can then use the Solver addin to minimize the sum of squared errors using the coefficients as "changing cells".

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[dcasto]

I cheat and double click on the graphed line and select the curve fit I want (ln, polynomial). Excel the puts the equation on the graph and I cut and paste the equation to a cell (you have to remove "y=" with just "=".

 

[jghrist]

You can also get the coefficients and statistical data into cells by highlighting a range (see Excel Help on LINEST or LOGEST) type the =LINEST() function in the edit line and press Ctrl+Shift+Enter to enter the function as an array. That way you don't have to use the INDEX function to get at the individual values.

 

[joerd]

that's true, but you'll get the output in Excel's pre-defined arrangement (i.e. coefficients in the first row, std.dev. in the next, etc.) The index formula is more flexible. It depends on what you want to do, and what you want your output to look like. The main drawback of Clyde's formulas is the repeated call to LINEST (3 or 4 times) where one time would be sufficient if you go with the standard output.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[TerryScan]

Check recent post: thread770-179377

 

[cellocgw]

There appears to be a bug in Excel2004 for MacOSX, 11.3.5 (at least). Here's an example of 2nd order polynomial fit. If I use the format
linest(yarray,xarray^{1,2},1,1)
the results are garbage. If I create two columns, one with x values and the next with x^2 values, then
linest(yarray, x:x2,1,1) works fine (assuming I've named the two columns "x" and "x2" ) .
The exact same spreadsheet seemed to produce the correct result using the first format when run under ExcelX for MacOSX (the older version of Office). So, there seems to be a problem under Excel2004 in expanding that power-array part of the formula.

Can anyone verify this, and does anyone know how I can contact a "useful" person at Microsoft to track this down?

 

[cellocgw]

Followup:
Several people on various microsoft/excel forums have told me this is a known bug in Excel2004 (OSX). The workaround is to use the transpose: (note the semicolon in there)

=LINEST(TRANSPOSE(y_range),TRANSPOSE(x_range)^{1;2},TRUE,TRUE)

The problem appears to be the way that xarray^{1,2} is returned to the
function.

Please report it again to MacBU (via XL's Help/Send Feedback). It may
not be too late to get it fixed in XL08.

 

[cub3bead]

Better solution is to download the sharware program CurveExpert from

http://www.ebicom.net/~dhyams/cmain.htm

It is a fantastic piece of shareware.

 

[cellocgw]

cub3bead:
You're absolutely correct - I've used CurveExpert for several years now.
An even better (more powerful) tool is Regress+, by Mike McLaughlin. However, that's MacOS only, which some of us regard as a bonus .

I fully agree with the rule that Excel should be the platform of last resort for just about anything that involves math.

 

[Koether]

Having trouble getting this to work for a 3rd degree curve. It keeps wanting to make c3 = 0 and then c2 and c1 are equal to c1 and c2 from a 2nd degree regression. I've checked the formulas like 10x so that is not the problem. I'm using Excel 2003, not on a mac. any ideas?

c3: =INDEX(LINEST(y,x^{1,2,3}),1)

 

[cellocgw]

Koether:
Have you tried dumping the complete LINEST output, i.e. selecting a 4columnx5row region and using shift-ctrl-return to get all the coefficients and params?
And just for completeness' sake, try using the
'transpose' version posted above to see what you get.
I just ran a linest(transpose,transpose(x^{1;2;3}) and got appropriate results (OSX Excel2004).

If that doesn't help, can you post your spreadsheet/data somewhere for me to try out?

Carl

 

[Koether]

I think I partly figured out what is going on, the macro that imports the data time stamps it, and with the excel time format I think it is causing some sort of overrun/rounding error. If I recalc the X values in terms of seconds from the first reading, (excel time format is decimal days.) I get a value for c3 and it follows the polynomial trend generated by excel in the graph. I can post up the sheet somewhere if you guys are curious.