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

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.



4. In the Input X Range edit box, type $B$1:$B$4.



5. Click Output Range, and enter $D$1 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 

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