Microsoft KB Archive/178614

= Duplicate Columns in Regression Summary Output Table =

Article ID: 178614

Article Last Modified on 8/17/2005

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q178614





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:

 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. Select Regression, and click OK. In the Input Y Range edit box, type $A$1:$A$4 . In the Input X Range edit box, type $B$1:$B$4 . Click Output Range, and enter $D$1 in the edit 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 down to cells 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 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. </li> On the Tools menu, click Data Analysis. Select Regression, and click OK.</li> Click to select the Confidence Level check box. Enter 85 in the edit box to the right of the check box.</li> Click OK. Click OK again to overwrite the existing table.</li></ol>

Note that the following 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: XL5 XL7 XL97 XL

Keywords: kbprb KB178614

-

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

© Microsoft Corporation. All rights reserved.