Getting Coefficients from a polynomial trendline into a cell for future calculations

[Guest]

Hi there
I have created a chart to display my data and fit a polynomial trendline. How can I get the coefficients for the trendline into a cell in the worksheet so I can, for example, calculate the interpolated maximum point of the parabola? I can't seem to find a way to do a regression other than linear in the Analysis toolpak. Currently I'm typing them in by hand, and it is getting tiresome
Thanks for any guidance
Bob

 

[Jon Peltier]

Bob -

Dave Braden has posted a macro that will extract the coefficients from
the trendline:

http://www.google.com/[email protected]

Alternatively, Bernard Liengme shows how to use Excel's LINEST worksheet
function to compute the coefficients in the worksheet.

- Jon

 

[i-Zapp]

http://www.j-walk.com/ss/excel/tips/tip101.htm

 

[MikeG]

-----Original Message-----
Hi there,
I have created a chart to display my data and fit a

polynomial trendline. How can I get the coefficients for
the trendline into a cell in the worksheet so I can, for
example, calculate the interpolated maximum point of the
parabola? I can't seem to find a way to do a regression
other than linear in the Analysis toolpak. Currently I'm
typing them in by hand, and it is getting tiresome.

Thanks for any guidance.
Bob
.
It's long but it works, I tried to write a macro but it

kept failing at the copy paste stage.
"Add trendline"
select "polynomial" order "6"
click "options" display equation
I suppose you already knew that.
right click over the equation gives "format data label"
click "number"
choose catagory "number"
10 decimal places OK
drag the mouse pointer over all the equation with left
mouse button down. This makes the equation all black.
"edit" "copy" and paste in any cell.
"edit" "replace"
replace "x" with *(cell you choose)^ example *AE53^.
then delete "y" and the ^ that replaced the first order x.
It's interesting to then plot the same graph using the
polynomial. If you don't use a high order and a high
number of decimal places the graph from the poly is often
nothing like the original.