XL: Erroneous Output Using Linear Regression (Linest)

• Microsoft Excel 97 for Windows
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel for Windows, versions 4.0, 5.0, 5.0c
• Microsoft Excel 98 Macintosh Edition
• Microsoft Excel for the Macintosh, versions 4.0, 5.0, 5.0a

SYMPTOMS

When you use the LINEST worksheet function on 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 your x-value and y-value ranges overlap, the LINEST worksheet function will produce 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 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 column).

Case 3: The User Specifies 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. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

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".