XL2000: Duplicate Columns in Regression Summary Output Table (214338)


The information in this article applies to:

  • Microsoft Excel 2000
This article was previously published under Q214338

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

In the example above, the columns the Regression tool displays are correct. The following items explain how the tool reaches this result.
  • 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%) 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 selected, the confidence level used is 95.0%; if the Confidence Level check box is selected, the value entered in the edit box is used.

MORE INFORMATION

In Microsoft Excel 2000, you can use the Analysis ToolPak add-in to perform a variety of data analysis functions, including linear regression analysis.

Steps to Reproduce the Problem

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. In the Data Analysis dialog box, click Regression and then click OK.
  3. In the Regression dialog box, in the Input Y Range box, type $A$1:$A$4.
  4. In Input X Range box, type $B$1:$B$4 .
  5. Under Output options, click Output Range, and then type $D$1 in the 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 to the cell range 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
    The first two columns display the lower and upper 95%, and the last two columns also display the lower and upper 95.0%. This is true because the Confidence Level check box in the Regression dialog box was not selected.
  7. On the Tools menu, click Data Analysis. In the Data Analysis dialog box, click Regression and then click OK.
  8. Click to select the Confidence Level check box. Type 85 in the box to the right of the check box.
  9. Click OK, and then click OK again to overwrite the existing table.
The following data 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%.
Modification Type:MajorLast Reviewed:9/27/2003
Keywords:kbprb KB214338
©2003 Microsoft Corporation. All rights reserved.