## 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 R

^{2}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