Extracting Polynomial Coefficients

[Ken Hardman]

Using Charts to ploy trend lines, there is an option to display a polynomial
curve fit of the data on the chart. Is there a way to extract the
coefficients of the curve for further analysis in the spreadsheet?

[[email protected]]

Ken
not tried this but it seems you used LINEST function on the data to get
what you need
I found the MS help a bit un-helpful but this link may help
http://www.stfx.ca/people/bliengme/E...Polynomial.htm
also much more with a Google search

you should be aware that the accuracy of LINEST does not always match that
of the Excel curve fitting. Again see the google search for more detail.

hth RES

[nsv]

The Excel help on polynomial fitting with LINEST is not very informative so your question is quite frequent. I have fought a lot with exactly the same problem AND solved it. Not very elegantly, but still it works and you get the coefficients in separate cells that are updated if input values change.

I have put the solution into a small worksheet with two different ways of finding trendlines. If you dare display your e-mail address I can forward it.

If not, just try this example where 13 sampled values are converted into a 3'rd degree polynomium:
Put your sampled Y-values in column A from A3 to A15
Put your sampled X-values in column A from B3 to B15
In cell C3 write =B3^2 and in D3 write =B3^3
Copy the C3:D3 to C3:D15
In cell E3 write =LINEST(A3:A15;B3:D15)
Mark cells E3 to H3 and press CTRL+SHIFT+ENTER (this enters an array formula), and your coefficients should appear.
Check it with the trend on a graph of your sampled values

The method should be able to create up 16'th degree polynomiums, but I haven't tried more than 6 and that works.

Niels

[nsv]

correction
Put your sampled X-values in column B from B3 to B15

[[email protected]]

NSV and Ken

I suggest you both follow the link I posted earlier for a neater solution

Using the example ranges
>Put your sampled Y-values in column A from A3 to A15
>Put your sampled X-values in column A from B3 to B15

The function for a 3rd degree
=LINEST(A3:A15,B3:B15^{1,2,3})
array entered into 4 cells (eg. A17:D17)
A17 = 3rd power coefficient
D17 is the intercept or Zero power coefficient

This is all layed out with pictures at

http://www.stfx.ca/people/bliengme/E...Polynomial.htm

give it a go it can't hurt <g>
RES

[nsv]

You're right - a much more effective solution, but difficult to figure out. Wonder why they don't put it in the Excel HELP text.

[Jerry W. Lewis]

The use of LINEST for this purpose has already been discussed.
Alternately, David Braden has posted VBA code to extract the
coefficients directly from the chart into cells

http://groups.google.com/groups?selm....microsoft.com

The advantage of using the chart coefficients (either manually or via
Braden's code) is that pre-2003 LINEST uses a numerically poor algorithm
that can give inaccurate results with some data sets. The chart
trendline (extracted by Braden's code) is much better numerically,
provided you format the equation to display to full precision
(scientific notation with 14 decimal places).

Jerry

Ken Hardman wrote:

> Using Charts to ploy trend lines, there is an option to display a polynomial
> curve fit of the data on the chart. Is there a way to extract the
> coefficients of the curve for further analysis in the spreadsheet?