Blank Cells in Input Range of Regression Tool Return Error
Article ID: 147184
Article Last Modified on 8/17/2005
APPLIES TO
- Microsoft Excel 97 Standard Edition
- Microsoft Excel 95 Standard Edition
- Microsoft Excel 5.0 Standard Edition
- Microsoft Excel 98 for Macintosh
This article was previously published under Q147184
SYMPTOMS
Microsoft Excel Versions 5.0 or Later
When you use the Regression tool from the Analysis ToolPak, 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.
Microsoft Excel Version 4.0
When you use the Regression tool from the Analysis ToolPak, if there are
blank cells in either the X range or the Y range, you receive #VALUE!
errors in the summary output range or ranges.
CAUSE
This problem 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, place zero
values in the blank cells for the x range or the y range.
Microsoft Excel 5.0 or Later
- Select the entire range used for the input values.
- Press F5.
- Click Special.
- In the Go To Special dialog box, click Blanks.
- Click OK.
- Type the numeral zero 0, and then press CTRL+ENTER (or CONTROL+RETURN
on the Macintosh).
Microsoft Excel 4.0
- Select the entire range used for the input values.
- On the Formula menu, click Select Special.
- Click Blanks.
- Click OK.
- Type the numeral zero 0, and then press ENTER (or RETURN on the
Macintosh).
- Repeat step 5 until all of the blank cells are replaced with the
numeral zero (0).
MORE INFORMATION
The add-in discussed in this article is provided "as is" and 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.
Additional query words: 5.0 8.00 97 98 XL98 XL97 XL7 XL5 XL4 ATP toolpack tool pak pack XL
Keywords: kbualink97 kbprb KB147184