Erroneous Output Is Returned Using Linear Regression (LINEST) (161545)
The information in this article applies to:
- Microsoft Excel 97 for Windows
- Microsoft Excel for Windows 95
- Microsoft Excel for Windows 5.0
- Microsoft Excel 98 Macintosh Edition
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)
WORKAROUNDCase 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".
| Modification Type: | Minor | Last Reviewed: | 8/17/2005 |
|---|
| Keywords: | kbbug kbualink97 KB161545 |
|---|
|