Description of the LINEST function in Excel 2003

[PsYcHo 1]

Hello Friends,
I want to know about the LINEST function that is being used in Excel 2003. The tutorial that I was referring was not having an enough explanation about it. So I thought that instead of searching on web, it would be much better to post here. I am looking for some description of the LINEST function in Excel 2003. I am not having much knowledge about the statistical functions, so please provide the detailed explanation.!!

[DANIEL 602]

LINEST (known_y, known_x, intersection, statistics) can perform a linear regression. A least squares criterion is established and LINEST attempting to find the fit that best meets this criterion. known_y represents the data on the dependent variable, while known_x represents data on one or more independent variables. The second argument is optional. If the second argument is omitted, the default is then a picture of the same size as the known_y that contains the values (1, 2, 3 and so on).

[Rob Dizzle]

The value TRUE is assigned the last argument if you want to get additional statistics (various sums of squares, R squared, F-statistics or standard errors of regression coefficients, for example). In this case, LINEST must be entered as an array formula. The last argument is optional, if omitted, its value is interpreted as FALSE. The dimensions of the table are five lines by a number of columns equals the number of independent variables plus one if the third argument set to TRUE (if the third argument is not TRUE, the number of columns is equal the number of independent variables). When FALSE is assigned to the third argument in Microsoft Excel 2002 and in earlier versions of Excel, a workaround is necessary.

[Bigga Lexx]

In most cases employment LINEST the intersection argument is TRUE. This indicates that you want the linear regression model included the possibility of a coefficient non-zero intercept in the model. If known_x is represented in the data columns and the value TRUE is assigned to the intersection LINEST then adds a data column filled with 1 as the data on an additional independent variable. FALSE should be assigned to the argument that intersection if you want to force the regression line to cross the origin. In Excel 2002 and in earlier versions of Excel, incorrect results are always returned when the value FALSE is assigned to this argument, at least in the detailed statistics are available from LINEST.

[Raine]

The performance of LINEST in earlier versions of Excel (or more precisely, the performance of the linear regression tool of Analysis ToolPak calling LINEST) have been rightly criticized (for details, see section "References" section). The linear regression tools of Excel problem posed mainly insofar as they do not address the problems of collinear explanatory variables (or nearly collinear). With data sets provided by the American Institute for Standards & Technology (NIST, National Institute for Standards and Technology, formerly National Bureau of Standards) designed to test the effectiveness of statistical software, numerical inaccuracies were found in the fields of linear regression, analysis of variance and nonlinear regression. In Excel 2003 and in later versions of Excel, these problems have been solved, except that of the non-linear regression is due to the Solver add-in functions, not statistics or to Analysis ToolPak.

[kattman]

LINEST used the "normal equations" to find the regression coefficients. This method is numerically less stable than the singular value decomposition or QR decomposition. The QR decomposition has been implemented in Excel 2003 and in later versions of Excel. he QR decomposition analysis effectively problems of collinearity and excludes any data column of the latest model if this column can be expressed as a sum of multiples of columns included. The quasi-collinearity is treated the same way. A set of columns is almost collinear if, when you try to express a column of data as a sum of multiples of other columns, the resulting adjustment is extremely close.