Microsoft KB Archive/161545

= Erroneous Output Is Returned Using Linear Regression (LINEST) =

Article ID: 161545

Article Last Modified on 8/17/2005

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q161545





SYMPTOMS
When you use the LINEST worksheet function in a worksheet, the statistical output may return incorrect values. The Regression tool under Data Analysis from the Analysis ToolPak may also return incorrect values.



CAUSE
The output returned from LINEST may be incorrect if any of the following conditions are true:


 * The range of x-values overlaps the range of y-values -or-


 * The number of rows in the input range is less than the number of columns in the total range (x-value plus y-value). -or-


 * The user specifies a zero constant (set the third argument of the LINEST function to True)



Case 1: The X-value and Y-value Ranges Overlap
If the x-value and y-value ranges overlap, the LINEST worksheet function produces erroneous values in all result cells. Normal statistical probability does not allow for the values in the x and y ranges to overlap (duplicate each other). Do not overlap the x- and y-value ranges when referencing cells in the formula.

NOTE: The Regression tool in Data Analysis alerts you of this problem and does not continue. Use Regression from the Data Analysis command on the Tools menu instead of using the LINEST worksheet function.

Case 2: The Number of Rows Is Less Than the Number of X-columns
It is not statistically valid for the number of rows to be less than the number of x (variable) columns. The number of rows of data must be greater than the number of columns of data (x-columns plus y-columns).

Case 3: You Specify a Zero Constant
Do not specify a zero constant (b=0) in the function.



STATUS
Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

