Microsoft KB Archive/214338

= XL2000: Duplicate Columns in Regression Summary Output Table =

Article ID: 214338

Article Last Modified on 9/27/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



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.   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  On the Tools menu, click Data Analysis. In the Data Analysis dialog box, click Regression and then click OK. In the Regression dialog box, in the Input Y Range box, type $A$1:$A$4 . In Input X Range box, type $B$1:$B$4 . Under Output options, click Output Range, and then type $D$1 in the box to the right of the option button.</li>  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: <pre class="fixed_text">  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. </li> On the Tools menu, click Data Analysis. In the Data Analysis dialog box, click Regression and then click OK.</li> Click to select the Confidence Level check box. Type 85 in the box to the right of the check box.</li> Click OK, and then click OK again to overwrite the existing table.</li></ol>

The following data appears in cells I16:L18: <pre class="fixed_text">  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: XL2000

Keywords: kbprb KB214338

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.