## Using the Regression Tool in Analysis Tools

Linear regression can also be performed using the Add-In package called the Analysis ToolPak. If the Analysis ToolPak Add-In is installed, the Data Analysis... command will be present at the bottom of the Tools menu; if the Data Analysis... command is not present in the Tools menu, choose Add-Ins... from the Tools menu and check the box for Analysis ToolPak or Analysis ToolPak (VBA) to install it. Now when you click on the Tools menu you will see the Data Analysis... command.

Data Analysis

 Covariance jd Descriptive Statistics Exponential Smoothing F-Test Two-Sample for Variances Fourier Analysis Histogram Moving Average _ Random Number Generation Rank and Percentile ^Regression

Figure 13-14. The Data Analysis dialog box.

Cancel

Help

Figure 13-14. The Data Analysis dialog box.

-Input—----------------------—

[\$D\$2:\$D\$14

Input i Range;

Input X Range:

(\$A\$2:\$C\$H

ii

F~ labels

f~ Constant is Zero

r Confidence Level:

%

C Output Range: f* New Worksheet Ply: C New Workbook -Residuals——'--

Output options-

C Output Range: f* New Worksheet Ply: C New Workbook -Residuals——'--

f~ Residuals

I Standardised Residuals f~ Residual Plots P Line Fit Plots

Normal Probability---

f" Normal Probability Plots

Cancel

Help

### Figure 13-15. The Regression dialog box.

After you choose Data Analysis... from the Tools menu, choose Regression from the Analysis Tools list box. The Regression dialog box (Figure 13-15) will prompt you to enter the range of dependent variable (y) values and the range of independent variable (x) values, as well as whether the constant is zero, whether the first cell in each range is a label, and the confidence level desired in the output summary. Then select a range for the summary table. You need select only a single cell for this range; it will be the upper left corner of the range. You can also request a table of residuals and a normal probability plot. If you select a cell or range such that the summary table would over-write cells containing values, you will get a warning message.

In contrast to the results returned by LI NEST, the output is clearly labeled, and additional statistical data are provided.

 A B C. D E | " t SUMMARY OUTPUT 3 Regression Statistics 4 Multiple R 0.999939163 5 R Square □ 999878342 6 Adjusted R Square 0.999837789 7 Standard Error 0.359817436 8 Observations 1 3 I 10 ANOVA 11 df ss IWS F i 12 Regression 3 9576.62709 3192.209 24656 244! 13 Residual 9 1.1 6521 7283 0 1294686 14 Total 12 9577.792308 IV 16 Coefficients Standard Error tStat P-t/3/ue 17 Intercept 32 09362637 0.307011363 104 551 92 3 4E-15 18 X Variable 1 -0 53858391 0 046189683 -11.860373 9.83E-07 19 X Variable 2 -0 QQ494605 0 001833799 -2,8971619 0.02450731 20 X Variable 3 -0.00017273 2 00596E-05 -8.6106907 1 224E-05j

Figure 13-16. Regression statistics returned by the Regression tool, (folder 'Chapter 13 Examples', workbook 'Dowtherm data', sheet 'Using Regression')

Figure 13-16. Regression statistics returned by the Regression tool, (folder 'Chapter 13 Examples', workbook 'Dowtherm data', sheet 'Using Regression')

0 0