TREND function for a polynomial fit results in a linear fit

[chenke01]

Hello, I am having an issue with the TREND function giving me a linear fit
when I need a polynomial fit. It seems to happen in the following situation:

X X^2 Y Fit
-4 16 -20 =TREND(Ycolumn,BothXcolumns)
-3.8 14.44 -19
.... ... ...
0 0 0
0.2 0.04 -1
.... ... ...
3.8 14.44 -19
4 16 -20

The resulting trend line is linear, but when you change the first X from -4
to -3.9 (or basically change any one number in that list), you get the
polynomial trend line that you would expect for this. Does anyone have any
insight to what might be causing this?

Thanks

 

[Jerry W. Lewis]

I will guess that you are using Excel 2003. LINEST in Excel 2003 introduced
a (usually) better numerical algorithm that unfortunately included a bug such
that coefficient estimates that are exactly zero are not to be trusted.
Since LINEST takes the quadratic coefficient to be zero, so does TREND. This
bug is fixed in Excel 2007.

AFAIK, this only happens when there are orthogonal columns in the model,
such x and x^2 with your particular values (SUMPRODUCT(xdata,x2data)~0).
Orthogonal columns happen to be the case where LINEST in versions prior to
2003 did not have numerical issues.

In general, when you get zero coefficient estimates, you can use matrix
functions to form and solve the normal equations (as was done prior to 2003).
For your particular data set, you know that the contribution of the linear
term should be zero, so you can use TREND(ydata,xdata^2) to get the correct
forcast or LINEST(ydata,xdata^2) to get the correct intercept and quadratic
coefficient.

As was noted with previously posted examples,
LINEST(Ycolumn,BothXcolumns,,TRUE) gives correct values in lines 2-5 of its
output (even the standard errors of the incorrect coefficients are correct!).
Thus this bug appears to be a consequence of implementing a matrix
equivalent of the ill-conceived (IMHO) "optimization" introduced in Excel 97
http://support.microsoft.com/default.aspx/kb/78113
that attempts to hide the impact of binary approximations at the expense of
numerical accuracy and standard properties of arithmetic when they guess
wrong.

Jerry

 

[chenke01]

Thanks Jerry. Very helpful

I will guess that you are using Excel 2003. LINEST in Excel 2003 introduced
a (usually) better numerical algorithm that unfortunately included a bug such
that coefficient estimates that are exactly zero are not to be trusted.
Since LINEST takes the quadratic coefficient to be zero, so does TREND. This
bug is fixed in Excel 2007.

AFAIK, this only happens when there are orthogonal columns in the model,
such x and x^2 with your particular values (SUMPRODUCT(xdata,x2data)~0).
Orthogonal columns happen to be the case where LINEST in versions prior to
2003 did not have numerical issues.

In general, when you get zero coefficient estimates, you can use matrix
functions to form and solve the normal equations (as was done prior to 2003).
For your particular data set, you know that the contribution of the linear
term should be zero, so you can use TREND(ydata,xdata^2) to get the correct
forcast or LINEST(ydata,xdata^2) to get the correct intercept and quadratic
coefficient.

As was noted with previously posted examples,
LINEST(Ycolumn,BothXcolumns,,TRUE) gives correct values in lines 2-5 of its
output (even the standard errors of the incorrect coefficients are correct!).
Thus this bug appears to be a consequence of implementing a matrix
equivalent of the ill-conceived (IMHO) "optimization" introduced in Excel 97
http://support.microsoft.com/default.aspx/kb/78113
that attempts to hide the impact of binary approximations at the expense of
numerical accuracy and standard properties of arithmetic when they guess
wrong.

Jerry