Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I solve for x in a second-order polynomial trend? | Charts and Charting in Excel | |||
Linear trend function: 5 year increment | Excel Discussion (Misc queries) | |||
Linear Regression using the TREND function | Excel Worksheet Functions | |||
Determining error associated with polynomial trend lines. | Charts and Charting in Excel | |||
trend for polynomial curve fitting by regressing | Excel Worksheet Functions |