Linest not matching the Chart Polynomal Trendline

[TSchulz85]

Hi,

I'm trying to do a Polynomial Regression, and managed to do it. However, I do get different values from those if I do a polynomial trendline with the Excel regression chart. I think I checked everything but still don't find my problem. Anyone has an idea what I'm doing wrong?
Please refer to the attached sheet.

Thanks a lot,
Tobias

[MrShorty]

It appears to me that the LINEST() function is detecting near collinearity with the x^1 term and forcing that coefficient to 0. The regression algorithms for the chart trendline appear to not detect the same collinearity.

Entering the chart trendline coefficients into the spreadsheet and calculating the estimate with those coefficients appears to give the exact same results -- so my guess is that LINEST() is correctly detecting the collinearity and that the two regressions are equivalent.

Discussion of the LINEST() function including a little about collinearity: http://support.microsoft.com/kb/828533

[joeu2004]

I'm trying to do a Polynomial Regression, and managed to do it. However, I do get different values from those if I do a polynomial trendline with the Excel regression chart. I think I checked everything but still don't find my problem. Anyone has an idea what I'm doing wrong?

I suggest that you array-enter the following modified formula into BM4:BS4:

=LINEST(D4:BK4,(D3:BK3-D3)^{1;2;3;4;5;6})

D3:BK3 have very large numbers (dates?). So for example, BK3^6 is a very large number relative BK3^1. I suspect this causes arithmetic differences internally within LINEST.

You might note that even with that change, LINEST returns different coefficients. That is allowed. The question is: do the LINEST coefficients and the trendline coefficients produce about the same "best fit" curve?

[EDIT] Even (BK3-D3)^6 is very large relatively. So even with that change, LINEST might still fail to produce reasonable coefficients. But give it a shot.

BTW, a simpler formula for D5 is (normally-entered; just press Enter):

=SUMPRODUCT($BM$4:$BS$4*D3^{6,5,4,3,2,1,0})

To compare, be sure to format the trendline "label" to always show coefficients with 15 significant digits. I prefer the Scientific format with 14 decimal places. Then copy and paste the trendline coefficients into Excel.

Note: I believe it is possible to have different "best fit" curves. But I would expect their RSQs to be reasonably close. I do not have the time now to try this with your example.

[joeu2004]

ERRATA:

I suggest that you array-enter the following modified formula into BM4:BS4:

=LINEST(D4:BK4,(D3:BK3-D3)^{1;2;3;4;5;6})

The formula must be:

=LINEST(D4:BK4,[B][COLOR="#FF0000"](D3:BK3-D3+1)^{1;2;3;4;5;6})

to be used with the simpler formula suggested for D5.

BTW, a simpler formula for D5 is (normally-entered; just press Enter):

=SUMPRODUCT($BM$4:$BS$4*D3^{6,5,4,3,2,1,0})

Use that formula when BM4:BS4 contains the trendline coefficients.

But for the modified LINEST formula, use the following formula to generate the "best fit" curve:

=SUMPRODUCT($BM$4:$BS$4*(D3-$D$3+1)^{6,5,4,3,2,1,0})

The expression D3-$D$3+1 will make more sense when the formula is copied to the right.

[shg]

I think charts and LINEST use different algorithms. Both give about the same r2.