How to put coefficients of trend line into spreadsheet?

[nomail1983]

I fit a 4th-order polynomial curve to data in a chart
and chose the option to display the equation. But when
I put that equation into cells in the spreadsheet, the
evaluated formula does not even come close to the
original Y values, even though I am usin the same X
values. I wonder if it is due to round-off error in
the displayed equation.

How can I get the exact coefficients of a trend line
-- specifically a polynomial of any order -- into a
spreadsheet?

PS: Is it mathematically true that there is always an
N-order polynomial that exactly fits N+1 data points?
That has been my experience so far.

 

[Guest]

Hi,

Maybe there is a direct way of getting the coefficients from the trendline
equation for a nonlinear fit; I am not aware of any. The following approach
which uses the Solver utility in Excel, however, can do the job.

Let us suppose that the X- values are in A2:A12, and Y-values are in B2:B12,
and you are fitting a 4th order polynomial to your Y-data.
In five helper cells (say D1, E1, F1, G1, and H1) enter 1. (These would be
the initial guess values for the coefficients; let's imagine that D1 contains
the coefficient for the 4th order term, E1 for the 3rd order term, ....., and
H1 contains the zero-order term, the constant).
In a new column, say C2:C12, calculate the Y value for each X value, using
the 4th order polynomial equation.
In C2, =$D$1*A2^4+$E$1*A2^3+$F$1*A2^2+$G$1*A2+$H$1.
Drag the formula down to C12.
Calculate the sum of the squares of the differences between the actual Y and
the calculated Y values (i.e., columns B and C) in a cell, say I1, using the
following formula.
=SUMXMY2(B2:B12,C2:C12) confirm with ENTER.

Now in the Solver, set the "Target Cell" as I1, check "Min", select D1:H1
for "By Changing Cells") and OK. The solver should optimize the
coefficients, by minimizing the sum of the squared deviations.

This method would work for ANY user defined function; so it is particularly
useful for function types that are not available with the trendline utility.

PS: I think that any set of N+1 data points is described by a
clearly-determined Nth order polynomial (since it is system of N+1
simultaneous equations with N+1 unknowns).

Regards,
B. R. Ramachandran

 

[nomail1983]

I fit a 4th-order polynomial curve to data in a chart
and chose the option to display the equation. But when
I put that equation into cells in the spreadsheet, the
evaluated formula does not even come close to the
original Y values, even though I am usin[g] the same X
values. I wonder if it is due to round-off error in
the displayed equation.

I confirmed that the problem is round-off error. When
I format the "data labels", increase the number of
decimal places displayed in the trendline formula and
enter those values manually into the spreadsheet, the
evaluated formula is close.

I still would prefer to access the exact coefficients
without having to transcribe the displayed values
manually.

 

[Jerry W. Lewis]

Yes, just as 2 points determines a straight line, so n+1 points
determins and nth degree polynomial.

Tushar Mehta has enhanced VBA code by David Braden to extract
coefficients from the chart trendline.
http://groups.google.com/group/microsoft.public.excel.charting/msg/0eda30f29434786d

Alternately you can compute then directly using the LINEST function
http://www.stfx.ca/people/bliengme/ExcelTips/Polynomial.htm

If you see significant differences in the coefficients, then the problem
is probably ill-conditioned, in which case LINEST coeffients may not be
reliable.

Jerry

 

[joeu2004]

you can compute then directly using the LINEST function
http://www.stfx.ca/people/bliengme/ExcelTips/Polynomial.htm

Perfect! Thanks.

 

[Jerry W. Lewis]

You're welcome.

Jerry

Perfect! Thanks.