Excel Curve Fit Coefficients
Excel Curve Fit Coefficients
(OP)
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.
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.





RE: Excel Curve Fit Coefficients
RE: Excel Curve Fit Coefficients
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(y),LN(x),,),1,2))
b: =INDEX(LINEST(LN(y),LN(x),,),1)
Exponential Trendline
Equation: y = c *e ^(b * x)
c: =EXP(INDEX(LINEST(LN(y),x),1,2))
b: =INDEX(LINEST(LN(y),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
RE: Excel Curve Fit Coefficients
You can obtain some statistical analysis data as well, to see how good your fit is. For example, the R2 value in case of a 2nd order polynomial:
r^2: =INDEX(LINEST(y,x^{1,2},TRUE,TRUE),3)
or in case of a log trendline:
r2: =INDEX(LINEST(y,LN(x),TRUE,TRUE),3)
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: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: Excel Curve Fit Coefficients
RE: Excel Curve Fit Coefficients
RE: Excel Curve Fit Coefficients
Cheers,
Joerd
Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: Excel Curve Fit Coefficients
RE: Excel Curve Fit Coefficients
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?
RE: Excel Curve Fit Coefficients
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.
RE: Excel Curve Fit Coefficients
It is a fantastic piece of shareware.
RE: Excel Curve Fit Coefficients
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.
RE: Excel Curve Fit Coefficients
c3: =INDEX(LINEST(y,x^{1,2,3}),1)
RE: Excel Curve Fit Coefficients
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(y),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
RE: Excel Curve Fit Coefficients