Coefficients from trendline

[aberdonian]

Dear Excel Gurus,

I am wondering if I can somehow pull the coefficients from the trendline
equation and display them in cells separately, e.g if the equation is

y = -5.21250E-21x5 + 2.35760E-17x4 + 7.99900E-13x3 - 4.69050E-08x2 -
1.56030E-03x + 6.86378E+01

I want the coefficients to be displayed in separate cells

-5.21250E-21
2.35760E-17
7.99900E-13
-4.69050E-08
-1.56030E-03
6.86378E+01

I am quite sure this can be done, since these coefficients are a
calculation, but how to do that? Correct me if I am wrong.

Thanks in advance.

Regards,
Excel User

 

[Bernard Liengme]

You can use LINEST as explained in
http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm

However, looking at the values of the first three coefficients, I doubt that
they are meaningful
best wishes

 

[excel user]

Thanks Bernard. This was very helpful. I agree the values are very small,
but if you multiply them by 18000 in the power of 5, 4 and 3 accordingly,
then they are meaningful...

Cheers,
Excel User

 

[Paul Hyett]

In microsoft.public.excel on Tue, 4 Dec 2007, excel user

Thanks Bernard. This was very helpful. I agree the values are very small,
but if you multiply them by 18000 in the power of 5, 4 and 3 accordingly,
then they are meaningful...

??

(18000*-5.21250E-21)^5 = -7.271*10^-81 by my calculation - so even less
meaningful...

 

[Jerry W. Lewis]

Set the optional 4th parameter of LINEST to TRUE and use the standard errors
of the coefficients (2nd line) to evaluate their significance. Loosely, the
coefficient should be at least twice its standard error. More precisely, use
TDIST(coef/stdErr,df) to get a p-value (should be small, say <0.05). If
these conditions do not hold and you still believe that the higher power
terms are real, then you need more data (probably spread over a much wider
range) to get decent estimates.

Jerry