XL: Blank Cells in Input Range of Regression Tool Return Error

ID: Q147184

The information in this article applies to:

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

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

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.

5. Click OK.

6. Type the numeral zero (0), and then press CTRL+ENTER (or CONTROL+RETURN

   on the Macintosh).

Microsoft Excel 4.0

1. Select the entire range used for the input values.

2. On the Formula menu, click Select Special.

3. Click Blanks.

4. Click OK.

5. Type the numeral zero (0), and then press ENTER (or RETURN on the

   Macintosh).

6. 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: 4.00 5.0 5.0c 5.00 7.00 8.00 97 98 XL98 XL97 XL7 XL5 XL4 ATP toolpack tool pak pack

Keywords          : kbtool kbualink97 xladdins 
Version           : WINDOWS:4.0,5.0,7.0,97; MACINTOSH:4.0,5.0,5.0a,98
Platform          : MACINTOSH WINDOWS
Issue type        : kbprb

Last Reviewed: May 17, 1999