XL: Duplicate Columns in Regression Summary Output Table

ID: Q178614

The information in this article applies to:

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

SYMPTOMS

If you use the Regression Data Analysis tool to perform a linear regression analysis, the output table created by the Regression tool may contain duplicate columns, as in the following example:

   Lower 95%     Upper 95%     Lower 95.0%     Upper 95.0%
        5             5               5               5
       -1            -1              -1              -1

CAUSE

This behavior is by design of the Regression tool.

The first two columns, Lower 95% and Upper 95%, are always displayed in the output table. These columns display the Intercept and X Variable for the lower 95% and upper 95%.
The last two columns (Lower 95.0% and Upper 95.0%, in this example) are also always displayed in the output table. These columns display the Intercept and X Variable for the Confidence Level specified in the Regression dialog box.
If the Confidence Level check box is not checked, the confidence level used is 95.0%; if the Confidence Level check box is checked, the value entered into the edit box is used.

MORE INFORMATION

In Microsoft Excel, you can use the Analysis ToolPak add-in to perform a variety of data analysis functions, including linear regression analysis. The following steps demonstrate the problem described earlier:

1. In Microsoft Excel, create a new workbook. In Sheet1, enter the

   following data:

      A1: 1   B1: 9
      A2: 2   B2: 8
      A3: 3   B3: 7
      A4: 4   B4: 6

2. On the Tools menu, click Data Analysis. Select Regression, and click

OK.

3. In the Input Y Range edit box, type "$A$1:$A$4" (without the quotation

   marks).

4. In the Input X Range edit box, type "$B$1:$B$4" (without the quotation

   marks).

5. Click Output Range, and enter "$D$1" (without the quotation marks) in

   the edit box to the right of the option button.

6. Click OK.

   The Summary Output table appears in the worksheet, starting in cell D1.
   If you scroll down to cells I16:L18, you see the following data:

   Lower 95%     Upper 95%     Lower 95.0%     Upper 95.0%
        10            10              10              10
        -1            -1              -1              -1

   Note that the first two columns display the lower and upper 95%, and
   that the last two columns also display the lower and upper 95.0%. This
   is true because the Confidence Level check box was not checked in the
   Regression dialog box.

7. On the Tools menu, click Data Analysis. Select Regression, and click

OK.

8. Click to select the Confidence Level check box. Enter 85 in the edit

   box to the right of the check box.

9. Click OK. Click OK again to overwrite the existing table.

Note that the following appears in cells I16:L18:

   Lower 95%     Upper 95%     Lower 85.0%     Upper 85.0%
        10            10              10              10
        -1            -1              -1              -1

The first two columns still display the lower and upper 95%; the last two columns now display the lower and upper 85.0%.

Additional query words: XL5 XL7 XL97

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

Last Reviewed: January 8, 1999