P_lease help with LINEST() fourth order polynomial results versus chart trendlin

Question

P_lease help with LINEST() fourth order polynomial results versus chart trendline equation in Excel 2003

I have a data set and tried to compare the results of LINEST() and the trendline equation. I used a fourth order polynomial and found that the LINEST() function results in different terms than the trendline equation. I'm using Excel 2003. I've used the LINEST() on other data with little issue. This particular data set results in LINEST() giving a 0 coefficient for the x^4 term (none of the terms match the trendline equation). Of course when I plot LINEST() generated data, the curves look different as well.

Would anyone happen to know what's happening?

Explanation / Answer

%^7&*There are several issues here:

A) Collinearity: According to MS Help: "...The LINEST function checks for collinearity and removes any redundant X columns from the regression model when it identifies them. Removed X columns can be recognized in LINEST output as having 0 coefficients in addition to 0 se values...."

B) Inaccurate calculations: Excel 2010 made some improvements to the LINEST algorithm including improved accuracy and allowing for many more regressors (previous limit was 16) http://office.microsoft.com/en-us/excel-help/what-s-new-changes-made-to-excel-functions-HA010355760.aspx#BM1

C) Model Choice: As others have said polynomial trends (degree>2) are rarely appropriate choices, there are many other methods to consider including pointwise interpolation and splines which will generally give more accuracy.

Turning to the second example set, I get the following results when comparing the x^4 coefficient using three methods in Excel 2010 against xNumbers(xRegPolyCoef with 30 digits of precision v6.5).

2.144936758821680E-12    xNumbers (v6.5)
2.144936758820720E-12    Linest (X-AVERAGE(X))^{1,2,3,4} (2010)
2.144936758820610E-12    Linest X^{1,2,3,4} (2010)
2.144936762689540E-12    Chart (2010)

Note the chart trendline has only 8 or so digits of accuracy in the x^4 coefficient. The lack of precision is likely why it is being dropped in the LINEST calculation in previous versions which I assume uses a similar algorithm and has comparable accuracy.

You can reduce collinearity by mean-centering the data and then extracting the coefficients. This applies to all versions and gives more accurate values.

In the case above, first define names... X:=$A$1:$A$1801, Y:=$A$1:$A$1801, N:={4,3,2,1,0} then enter in a 5x1 range with CTRL+SHIFT+ENTER:

=MMULT(LINEST(Y,(X-AVERAGE(X))^{1,2,3,4}),IFERROR(COMBIN(TRANSPOSE(N),N)*(-AVERAGE(X))^(TRANSPOSE(N)-N),0))

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.