Erroneous output is returned using Linear Regression (LINEST) in Excel (214230)

SYMPTOMS

When you use the LINEST worksheet function in a worksheet, the statistical output may return incorrect values. The Regression tool (click Data Analysis on the Tools menu) in 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.
The number of rows in the input range is less than the number of columns in the total range (x-value plus y-value).
You specify 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 alerts you to this problem and does not continue. Click Data Analysis on the Tools menu, and then click Regression, 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.

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