XL2000: Blank Cells in Input Range of Regression Tool Return Error Message (214254)


The information in this article applies to:

  • Microsoft Excel 2000
This article was previously published under Q214254

SYMPTOMS

When you use the Regression tool from the Analysis ToolPak in Microsoft Excel versions 5.0 or later, if there are blank cells in either the X range or the Y range, the Regression tool halts and you receive the following error message:
Regression - LINEST() function returns error. Please check input ranges again.

CAUSE

This behavior occurs because the LINEST function is unable to correctly calculate results if you use blank input values.

WORKAROUND

To correctly calculate results from the Regression tool, follow these steps:
  1. Select the entire range used for the input values.
  2. Press F5.
  3. Click Special.
  4. In the Go To Special dialog box, click Blanks, and then click OK.
  5. Type 0 (zero), and then press CTRL+ENTER.

MORE INFORMATION

The add-in that is discussed in this article is provided as-is. Microsoft does not guarantee that it can be used in all situations. Although Microsoft Support Professionals can help with the installation and existing functionality of this add-in, they will not modify the add-in to provide new functionality.

REFERENCES

For more information about regression analysis, click Microsoft Excel Help on the Help menu, type regression analysis tool in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MajorLast Reviewed:11/5/2003
Keywords:kbprb KB214254
©2003 Microsoft Corporation. All rights reserved.