Schmuller J. Statistical Analysis with Excel For Dummies

Подождите немного. Документ загружается.

279

Chapter 14: Regression: Linear and Multiple

Tabled output

Figure 14-16 shows the upper half of the tool’s tabled output, after I expanded

the columns. The title is SUMMARY OUTPUT. This part of the output features

one table for Regression Statistics, another for ANOVA, and one for the

regression coefficients.

Figure 14-16:

The upper

half of the

Regression

data analy-

sis tool’s

tabled

output.

The first three rows of the Regression Statistics table present information

related to R

, a measure of the strength of the SAT-GPA relationship in the

sample. The fourth row shows the standard error of estimate and the fifth

gives the number of individuals in the sample.

The ANOVA table shows the results of testing

: σ

Regression

≤ σ

Residual

: σ

Regression

> σ

Residual

If the value in the F-significance column is less than .05 (or whatever alpha

level you’re using), reject H

. In this example, it’s less than .05.

Just below the ANOVA table is a table that gives the information on the

regression coefficients. Excel doesn’t name it, but I refer to it as the coef-

ficients table. The Coefficients column provides the values for the intercept

and the slope. The slope is labeled with the name of the x-variable. The

Standard Error column presents the standard error of the intercept and the

standard error of the slope.

The remaining columns provide the results for the t-tests of the intercept and

the slope. The P-value column lets you know whether or not to reject H

for

each test. If the value is less than your alpha, reject H

. In this example, the

decision is to reject H

for the slope, but not for the intercept.

20 454060-ch14.indd 27920 454060-ch14.indd 279 4/21/09 7:33:56 PM4/21/09 7:33:56 PM

280

Part III: Drawing Conclusions from Data

Figure 14-17 shows the lower half of the Regression tool’s tabled output.

Figure 14-17:

The lower

half of the

Regression

data analy-

sis tool’s

tabled

output.

Here, you find the RESIDUAL OUTPUT and the PROBABILITY OUTPUT. The

RESIDUAL OUTPUT is a table that shows the predicted value and the residual

(y-y’) for each individual in the sample. It also shows the standard residual for

each observation, which is

The tabled data on residuals and standard residuals are useful for analyzing

the variability around the regression line. You can scan these data for outli-

ers, for example, and see if outliers are associated with particular values of

the x-variable. (If they are, it might mean that something weird is going on in

your sample.)

The PROBABILITY OUTPUT is a table of the percentiles in the y-variable data

in the sample. (Yes, PERCENTILE OUTPUT would be a better name.)

Graphic output

Figures 14-18, 14-19, and 14-20 show the Regression tool’s graphic output.

The Normal Probability Plot in Figure 14-18 is a graphic version of the

PROBABILITY OUTPUT table. The SAT Residual Plot in Figure 14-19 shows

the residuals graphed against the x-variable: For each SAT score in the

sample, this plot shows the corresponding residual. Figure 14-20 shows the

SAT Line Fit Plot — a look at the observed and the predicted y-values. Note

that in the Line Fit Plot, the SATs do not appear on the x-axis in numerical

order. Rather, they appear in the same order as in the data.

20 454060-ch14.indd 28020 454060-ch14.indd 280 4/21/09 7:33:57 PM4/21/09 7:33:57 PM

281

Chapter 14: Regression: Linear and Multiple

Figure 14-18:

The Normal

Probability

Plot shows

the percen-

tile data

for the

y-variable.

Figure 14-19:

The SAT

Residual

plot

graphs the

residuals

against the

x-variable.

Figure 14-20:

The SAT

Line Fit Plot

shows the

observed

y-values

and the

predicted

y-values.

20 454060-ch14.indd 28120 454060-ch14.indd 281 4/21/09 7:33:57 PM4/21/09 7:33:57 PM

282

Part III: Drawing Conclusions from Data

If you’ve used the Regression Tool in earlier versions of Excel, you’ll notice a

change in the Normal Probability Plot and the Line Fit Plot. These two formerly

appeared as scatterplots. In Excel 2007, they appear as column charts. You can

use Excel’s graphics capabilities to easily reformat them to scatterplots.

Juggling Many Relationships

at Once: Multiple Regression

Linear regression is a great tool for making predictions. When you know the

slope and the intercept of the line that relates two variables, you can take

a new x-value and predict a new y-value. In the example I’ve been working

through, you take a SAT score and predict a GPA for a Sahutsket University

student.

What if you knew more than just the SAT score for each student? What if you

had the student’s high-school average (on a 100 scale), and you could use

that information, too? If you could combine SAT with HS average, you might

have a more accurate predictor than just SAT alone.

When you work with more than one independent variable, you’re in the realm

of multiple regression. As in linear regression, you find regression coefficients

for the best-fitting line through a scatterplot. Once again, “best-fitting” means

that the sum of the squared distances from the data points to the line is a

minimum.

With two independent variables, however, you can’t show a scatterplot in

two dimensions. You need three dimensions, and that becomes difficult to

draw. Instead, I’ll just show you the equation of the regression line:

For the SAT-GPA example, that translates to

You can test hypotheses about the overall fit, and about all three of the

regression coefficients.

I won’t go through all the formulas for finding the coefficients, because that

gets really complicated. Instead, I’ll go right to the Excel capabilities.

20 454060-ch14.indd 28220 454060-ch14.indd 282 4/21/09 7:33:58 PM4/21/09 7:33:58 PM

283

Chapter 14: Regression: Linear and Multiple

A couple of things to bear in mind before I proceed:

✓ You can have any number of x-variables. I just use two in the upcoming

example.

✓ Expect the coefficient for SAT to change from linear regression to mul-

tiple regression. Expect the intercept to change, too.

✓ Expect the standard error of estimate to decrease from linear regression

to multiple regression. Because multiple regression uses more informa-

tion than linear regression, it reduces the error.

Excel Tools for Multiple Regression

The good news about Excel’s multiple regression tools is that they’re the

some of the same ones I just told you about for linear regression: You just

use them in a slightly different way.

The bad news is . . . well . . . uh . . . I can’t think of any bad news!

TREND revisited

I begin with TREND. Before, I showed you how to use this function to predict

values based on one x-variable. Change what you enter into the dialog box,

and it predicts values based on more than one.

Figure 14-21 shows the TREND dialog box and data for 20 students. In the

data, I’ve added a column for each student’s high-school average. I defined

HS_Average as the name for the data in this column. The Figure also shows

the selected column for TREND’s predictions. I include the Formula Bar in

this screen shot so you can see what the formula looks like.

1. With the data entered, select a column for TREND’s answers.

I selected F3:F22. That puts the predicted GPAs right next to the sample

GPAs.

2. From the Statistical Functions menu, select TREND to open the

Function Arguments dialog box for TREND.

20 454060-ch14.indd 28320 454060-ch14.indd 283 4/21/09 7:33:58 PM4/21/09 7:33:58 PM

284

Part III: Drawing Conclusions from Data

Figure 14-21:

The Function

Arguments

dialog box

for TREND

along with

data. TREND

is set up

to predict

GPAs for

the sample

SATs and

high-school

averages.

3. In the Function Arguments dialog box, enter the appropriate values

for the arguments.

In the Known_y’s box, I entered the name of the cell range that holds the

scores for the y-variable. For this example, that’s GPA (E3:E22).

In the Known_x’s box, I entered the names of the cell ranges that hold

the scores for the x-variables. With the cell ranges named, it’s SAT:HS_

Average. These names cover C3:D22, the cells that hold the SAT scores

and the high-school averages.

I left the New_x’s box blank.

In the Const box, the choices are TRUE (or leave it blank) to calculate

the y-intercept, or FALSE to set the y-intercept to zero. I entered TRUE. (I

really don’t know why you’d enter FALSE.) A note of caution: In the dialog

box, the instruction for the Const box refers to b. That’s the y-intercept.

Earlier in the chapter, I use a to represent the y-intercept, and b to repre-

sent the slope. No particular usage is standard for this. Also, the dialog

box makes it sound like this function just works for linear regression. As

you’re about to see, it works for multiple regression, too.

4. IMPORTANT: Do NOT click OK. Because this is an array function, press

Ctrl+Shift+Enter to put TREND’s answers into the selected column.

Figure 14-22 shows the answers in F3:F22. Note the difference in the Formula

Bar from Figure 14-21 to Figure 14-22. After the function completes its work,

Excel adds curly brackets to indicate an array formula.

So TREND predicts the values, and I haven’t even shown you how to find the

coefficients yet!

20 454060-ch14.indd 28420 454060-ch14.indd 284 4/21/09 7:33:58 PM4/21/09 7:33:58 PM

285

Chapter 14: Regression: Linear and Multiple

Figure 14-22:

The results

of TREND:

Predicted

GPAs for

the sample

SATs and

high-school

averages.

LINEST revisited

To find the multiple regression coefficients, I turn again to LINEST.

In Figure 14-23, I’ve put the data and the dialog box for LINEST, along with the

data and the selected array for the answers. The selected array is five-rows-

by-three-columns. It’s always five rows. The number of columns is equal to

the number of regression coefficients. For linear regression, it’s two— the

slope and the intercept. For this case of multiple regression, it’s three.

Figure 14-23:

The

Function

Arguments

dialog box

for LINEST,

along with

the data and

the selected

array for the

results of

a multiple

regression.

20 454060-ch14.indd 28520 454060-ch14.indd 285 4/21/09 7:33:58 PM4/21/09 7:33:58 PM

286

Part III: Drawing Conclusions from Data

Here are the steps for using LINEST for multiple regression with three

coefficients:

1. With the data entered, select a five-row-by-three-column array of cells

for LINEST’s results.

I selected H3:J7.

2. Click the Insert Function button to open the Insert Function dialog box.

3. In the Insert Function dialog box, select LINEST and click OK to open

the Function Arguments dialog box for LINEST.

In the Known_y’s box, enter the column that holds the scores for the

y-variable. For this example, that’s E3:E22, the GPAs.

In the Known_x’s box, enter the columns that hold the scores for the

x-variables .For this example, it’s C3:D22, the SAT scores and the high-

school averages.

In the Const box, enter TRUE (or leave it blank) to calculate the y-intercept.

Enter FALSE to set the y-intercept to zero. I typed TRUE.

In the Stats box, enter TRUE to return regression statistics in addition

to the slope and the intercept, FALSE (or leave it blank) to return just

the slope and the intercept. I entered TRUE. The dialog box refers to the

intercept as b and to the other coefficients as m-coefficients. I use a to rep-

resent the slope and b to refer to the other coefficients. No set of symbols

is standard.

4. IMPORTANT: Do NOT click OK. Because this is an array function,

press Ctrl+Shift+Enter to put LINEST’s answers into the selected array.

Figure 14-24 shows LINEST’s results. They’re not labeled in any way, so I

added the labels for you in the worksheet. I also drew a box around part of

the results to clarify what goes with what.

The entries that stand out are the ugly #N/A symbols in the last three rows of

the rightmost column. These indicate that LINEST doesn’t put anything into

these cells.

The top two rows of the array provide the values and standard errors for the

coefficients. I drew the box around those rows to separate them from the

three remaining rows, which present information in a different way. Before I

get to those rows, I’ll just tell you that the top row gives you the information

for writing the regression equation:

20 454060-ch14.indd 28620 454060-ch14.indd 286 4/21/09 7:33:59 PM4/21/09 7:33:59 PM

287

Chapter 14: Regression: Linear and Multiple

In terms of SAT, GPA, and high-school average, it’s:

The third row has R Square (a measure of the strength of the relationship

between GPA and the other two variables, which I cover in Chapter 15) and

the standard error of estimate. Compare the standard error of estimate for

the multiple regression with the standard error for the linear regression, and

you’ll see that the multiple one is smaller. (Never mind. I’ll do it for you. It’s

.40 for the linear and 0.35 for the multiple.)

The fourth row shows the F-ratio that tests the hypothesis about whether

or not the line is a good fit to the scatterplot, and the df for the denominator

of the F. The df for the numerator (not shown) is the number of coefficients

minus 1. You can use FINV to verify that this F with df = 2 and 17 is significant.

The last row gives you SS

Regression

and SS

Residual

Figure 14-24:

LINEST’s

multiple

results in

the selected

array.

Regression data analysis tool revisited

In the same way you use TREND and LINEST for multiple regression, you use

the Regression data analysis tool. Specify the appropriate array for the x-vari-

ables, and you’re off and running.

Here are the steps:

1. Type the data into the worksheet, and include labels for the columns.

2. Select Data | Data Analysis to open the Data Analysis dialog box.

20 454060-ch14.indd 28720 454060-ch14.indd 287 4/21/09 7:33:59 PM4/21/09 7:33:59 PM

288

Part III: Drawing Conclusions from Data

3. In the Data Analysis dialog box, scroll down the Analysis Tools list

and select Regression. Click OK to open the Regression dialog box.

This is the dialog box in Figure 14-15.

4. In the Input Y Range box, enter the cell range that holds the data for

the y-variable.

The GPAs (including the label) are in $E$2:$E$22. Note the $-signs for

absolute referencing.

5. In the Input X Range box, enter the cell range that holds the data for

the x-variable.

The SATs and the high-school averages (including the labels) are in

$C$2:$D$22.

6. If the cell ranges include column headings, select the Labels checkbox.

I included the labels in the ranges, so I selected the box.

7. The Alpha box has 0.05 as a default. Change that value if you want a

different alpha.

In the Output Options, select a radio button to indicate where you want

the results.

8. I selected New Worksheet Ply to put the results on a new page in the

worksheet.

9. The Residuals area provides four capabilities for viewing the devia-

tions between the data points and the predicted points. Select as many

as you like.

I selected all four.

10. The option in the Normal Probability Plot area produces a graph of

the percentiles of the y-variable.

I checked this one.

11. Click OK.

Go back to the section, “Data Analysis Tool: Regression,” for the details of

what’s in the output. It’s the same as before, with a couple of changes and

additions because of the new variable. Figure 14-25 shows the ANOVA table

and the coefficients table.

The ANOVA table shows the new df (2, 17, and 19 for Regression, Residual,

and Total, respectively). The coefficients table adds information for the HS

Average. It shows the values of all the coefficients, as well as standard errors,

and t-test information for hypothesis testing.

20 454060-ch14.indd 28820 454060-ch14.indd 288 4/21/09 7:33:59 PM4/21/09 7:33:59 PM