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?

 

[Robert_Steel]

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/ExcelTips/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 C33 to C315
In cell E3 write =LINEST(A3:A15;B315)
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

 

[Robert_Steel]

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. A1717)
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/ExcelTips/Polynomial.htm

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

 

[nsv]

You're right - a much more effective solution, but difficult to figur
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/[email protected]

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