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

" wrote:

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.