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)
WORKAROUND
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.
REFERENCES
Statistical Computations on a Digital Computer. William J. Hemmerle.
Blaisdell Publishing Company: 1967. Chapter 3, "Multiple-Regression
Computations" and section 3.2.1, "Preliminary Regression Theory".
Additional query words: 98 XL98 97 XL97 8.00 xl7 xl5 linear regression XL
Keywords: kbbug kbualink97 KB161545