What are an improvements of LINEST function in Excel 2003?

[SKREECH]

Sponsored Links

Hi friends,
I have done some basic things of the statistical functions. I have been told to write about an improvements of the Linest function. I have an idea about the Linest function but I don't know the improvements of it. So thought to ask you guys about it. Please tell me what are an improvements of LINEST function in Excel 2003? Hope that you all understood what I want to say.?!? Please reply me soon.!!

[Eric B]

The following improvements have been made: the correction formula for the sum of squares when the third argument LINEST set to FALSE and transition to the QR decomposition method to determine the regression coefficients. The QR decomposition has two advantages :

1. better numerical stability (rounding errors usually smaller);
2. analysis problems of collinearity.

Collinearity remains a major challenge, especially after having performed the tests on data-sets from NIST.

[JonathanD]

Regardless of the value of the third argument, LINEST was calculated using a method that ignores the problems of collinearity. The presence of collinearity caused rounding errors, and standard errors of regression coefficients and degrees of freedom inadequate. The rounding issues were sometimes so large that LINEST filled its output table with # NUM!. Generally, LINEST gives satisfactory results when the following conditions are met :

• No column of the explanatory variables are collinear (or nearly collinear).
• The third argument LINEST is TRUE or omitted.

The resolution of regression coefficients using the "normal equations" However, more likely to cause rounding errors than using the method of QR decomposition used in Excel 2003 and in later versions of Excel. Even if these factors cause more rounding errors, they do not pose a risk in most practical cases.

[ThoMas321]

LINEST has been greatly improved in Excel 2003 and in later versions of Excel. If you are using an earlier version of Excel, make sure the columns of explanatory variables are not collinear before using LINEST. It is recommended to use the workaround suggested in this article when the third argument LINEST set to FALSE. Note that collinearity does not pose a problem in a small percentage of cases and appeals to LINEST when the third argument set to FALSE are also relatively rare in practice.

[Kirmiac]

Earlier versions of Excel gives satisfactory results with LINEST in the absence of collinearity or when the third argument LINEST is TRUE or omitted. Improvements in LINEST concern the linear regression tool of Analysis ToolPak calling LINEST and related functions include :

• TREND
• LOGEST
• GROWTH

[Rob Dizzle]

LINEST used an incorrect formula to find the total sum of squares when the third argument LINEST was set to FALSE. This formula gave erroneous results in the sum of squares regression, and for outputs that depend on R squared and F statistic. The columns of variables form a matrix. If the intersection is adjusted, there is actually an additional column 1 that is not on your worksheet. The QR decomposition determines the order of this matrix. The preceding formulas for the DF regression must be replaced by the following formulas :

1. If "adjustment" Regression DF equals the order of the columns of the matrix of explanatory variables minus one'
2. In case of "non-adjustment" Regression DF equals the order of the columns of the matrix of explanatory variables.