Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default TREND function for a polynomial fit results in a linear fit

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 837
Default TREND function for a polynomial fit results in a linear fit

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" wrote:

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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default TREND function for a polynomial fit results in a linear fit

Thanks Jerry. Very helpful

"Jerry W. Lewis" wrote:

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" wrote:

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I solve for x in a second-order polynomial trend? DianeD Charts and Charting in Excel 2 February 23rd 07 04:04 PM
Linear trend function: 5 year increment Angie Excel Discussion (Misc queries) 2 September 15th 06 05:07 PM
Linear Regression using the TREND function scarlett1 Excel Worksheet Functions 2 May 26th 06 03:04 PM
Determining error associated with polynomial trend lines. BJ Richter Charts and Charting in Excel 1 April 21st 06 10:16 PM
trend for polynomial curve fitting by regressing vijaya Excel Worksheet Functions 7 November 11th 05 03:51 PM


All times are GMT +1. The time now is 05:42 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"