Coefficients from trendline

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

aberdonian - 04 Dec 2007 13:32 GMT

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

Reply to this Message

Bernard Liengme - 04 Dec 2007 13:53 GMT

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

Signature

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

> Dear Excel Gurus,
>
[quoted text clipped - 20 lines]
> Regards,
> Excel User

Reply to this Message

excel user - 04 Dec 2007 13:59 GMT

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

> You can use LINEST as explained in
> http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm
[quoted text clipped - 26 lines]
>> Regards,
>> Excel User

Reply to this Message

Paul Hyett - 04 Dec 2007 17:53 GMT

In microsoft.public.excel on Tue, 4 Dec 2007, excel user
<pochta007-spam@yahoo.com> wrote :

>>> 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
[quoted text clipped - 14 lines]
>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...

Signature

Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)

Reply to this Message

Jerry W. Lewis - 04 Dec 2007 21:34 GMT

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

> 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,
[quoted text clipped - 33 lines]
> >> Regards,
> >> Excel User

Reply to this Message