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

Analysis Tools

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

Post a comment