XL4: Regression Tool Returns #DIV/0 and #NUM! Error Values

• Microsoft Excel for Windows, versions 4.0, 4.0a
• Microsoft Excel for the Macintosh, version 4.0

SYMPTOMS

In Microsoft Excel 4.x, the Regression analysis tool (included with the Analysis ToolPak add-in macro) returns a #DIV/0! error value for all t- statistics and P-values, and a #NUM! error value for all Lower 95-percent and Upper 95-percent values in the summary output range if the following condition exists:

   the total number of y values = the number of X ranges + 1

WORKAROUND

To work around this problem, add or remove a row of data from the range, and recalculate the regression.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in Microsoft Excel version 5.0.

MORE INFORMATION

The Regression tool returns a summary output range of regression statistics about the x and y data. If the matrix of data is square, that is, the number of y values is equal to the number of x ranges plus one, errors are returned to the t-statistics, P-values, and upper- and lower-percentage values.

REFERENCES

"User's Guide 2," version 4.0, Pages 41-45