Polynomial Treandline Coefficients

[Matt J]

I used the "add trendline" in Excel 2000 to do a 3rd order poly curve fit of
some data. Works great and I chose the "display equation" feature so I can
see the resulting polynomial coefficients, BUT, how can I automatically use
these coefficients in further calculations? Are they, can they be, available
in cells? Used elsewhere, for a slope (derivative) function for instance?

 

[David]

Hi. I tried to do this once before also. I wanted to find the points on the
trend line to work with them. I was trying with the 6th degree and the
numbers got to large for Excel to do with, but maybe with the 3rd degree you
can do it. You need to put the formula in a cell and actualy use the formula
to find the data points. I was trying to find stock market closing and a
derivative, but was not able to in the end.

 

[Matt J]

David,
Sure, I can retype the polyonomial in a cell and then use the equation but
what I was trying to do was automatically extract and use the coefficients in
further calculations without having to retype them because the data that was
curve fit was for one piece of a product that was going to be mass produced
but may have slightly different data from each piece.
Thanks anyway. Oter ideas anybody?

 

[S Davis]

Dont have anything in front of me right now, but Id imagine the help
will show the necessary formulas within excel such as linest, etc. What
was your original problem and how was your data set up? How do you know
that in future you will always want a polynomial trendline and not a
different, better fit estimate? That makes it a bit difficult to
establish a catch all formula

 

[Matt J]

Each set of data will be fairly close to each other, within ~+/-10% or so. In
order to demonstrate specification compliance I need to curve fit, calculate
the slope and compare to a reference. The data set is only 10 points. I was
hoping I could have Excel do this for me but I may have to write a macro or
VB routine.

 

[Jerry W. Lewis]

The simplest way is to use the TREND or LINEST worksheet functions.
http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm
shows how to specify polynomials to them. If you are overfitting the data,
you may have numerical issues with versions prior to 2003. Beware in 2003 if
a coefficient happens to be exactly zero.

Alternately, 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

Jerry

 

[Matt J]

Thanx!!!! the first approach using LINEST and entering the way you suggested
in the first link works great. I wonder why this wasn't described in the
excel help section.
Thanks again.