XL: Duplicate Columns in Regression Summary Output Table

• 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

In the versions of Microsoft Excel listed at the beginning of this article, 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.

STATUS

This behavior is by design of the Regression Data Analysis tool in the versions of Microsoft Excel listed at the beginning of this article.

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 above:

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. Select the Output Range option button, 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.

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