Coefficients from trendline

  • Thread starter Thread starter aberdonian
  • Start date Start date
A

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
 
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
 
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...
Click to expand...

??

(18000*-5.21250E-21)^5 = -7.271*10^-81 by my calculation - so even less
meaningful...
 
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
 
Back
Top