Schmuller J. Statistical Analysis with Excel For Dummies

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

249

Chapter 13: Slightly More Complicated Testing

Here are the hypotheses:

: μ

Spoken

= μ

Text

: Not H

and

: μ

Humorous

= μ

Technical

: Not H

Because the two presentation methods (Spoken and Text) are in the rows,

I refer to Presentation Type as the row factor. The two presentation styles

(Humorous and Technical) are in the columns, so Presentation Style is the

column factor.

Interactions

When you have rows and columns of data, and you’re testing hypotheses

about the row factor and the column factor, you have an additional consid-

eration. Namely, you have to be concerned about the row-column combina-

tions. Do the combinations result in peculiar effects?

For the example I presented, it’s possible that combining Spoken and Text

with Humorous and Technical yields something unexpected. In fact, you can

see that in the data in Figure 13-3: For Spoken presentation, the Humorous

style produces a higher average than the Technical style. For Text presenta-

tion, the Humorous style produces a lower average than the Technical style.

A situation like that is called an interaction. In formal terms, an interaction

occurs when the levels of one factor affect the levels of the other factor dif-

ferently. The label for the interaction is row factor × column factor, so for this

example that’s Method × Type.

The hypotheses for this are:

: Presentation Method does not interact with Presentation Style

: Not H

19 454060-ch13.indd 24919 454060-ch13.indd 249 4/21/09 7:33:08 PM4/21/09 7:33:08 PM

250

Part III: Drawing Conclusions from Data

The analysis

The statistical analysis, once again, is an analysis of variance (ANOVA). As is

the case with the other ANOVAs I showed you, it depends on the variances in

the data.

The first variance is the total variance, labeled MS

. That’s the variance of all

16 scores around their mean (the “grand mean”), which is 44.81:

The denominator tells you that df = 15 for MS

The next variance comes from the row factor. That’s MS

Method

, and it’s the

variance of the row means around the grand mean:

The 8 multiplies each squared deviation because you have to take into

account the number of scores that produced each row mean. The df for

Method

is the number of rows – 1, which is 1.

Similarly, the variance for the column factor is

The df for MS

Style

is 1 (the number of columns – 1).

Another variance is the pooled estimate based on the variances within the

four row-column combinations. It’s called the MS

Within

, or MS

. (For details on

and pooled estimates, see Chapter 12.). For this example,

This one is the error term (the denominator) for each F that you calculate. Its

denominator tells you that df = 12 for this MS.

19 454060-ch13.indd 25019 454060-ch13.indd 250 4/21/09 7:33:08 PM4/21/09 7:33:08 PM

251

Chapter 13: Slightly More Complicated Testing

The last variance comes from the interaction between the row factor and the

column factor. In this example, it’s labeled MS

Method X Type

. You can calculate

this a couple of ways. The easiest way is to take advantage of this general

relationship:

And this one:

Another way to calculate this is

The MS is

For this example,

To test the hypotheses, you calculate three Fs:

For df = 1 and 12, the critical F at α = .05 is 4.75. (You can use the Excel

function FINV to verify.). The decision is to reject H

for the Presentation

Method and for the Method × Style interaction, and to not reject H

for the

Presentation Style.

19 454060-ch13.indd 25119 454060-ch13.indd 251 4/21/09 7:33:08 PM4/21/09 7:33:08 PM

252

Part III: Drawing Conclusions from Data

Data analysis tool: Anova: Two-Factor

With Replication

Excel provides a data analysis tool that handles everything. This one is called

Anova: Two-Factor With Replication. “Replication” means you have more

than one score in each row-column combination.

Figure 13-4 shows this tool’s dialog box along with the data for the batteries-

tasks example.

Figure 13-4:

The Anova:

Two Factor

With

Replication

data

analysis

tool dialog

box along

with the

type-method

data.

The steps for using this tool are:

1. Enter the data into the worksheet and include labels for the rows and

columns.

For this example, the labels for the presentation methods are in cells B3

and B7. The presentation types are in cells C2 and D2. The data are in

cells C3 through D10.

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

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

and select Anova: Two Factor With Replication. Click OK to open the

select Anova: Two Factor With Replication dialog box.

This is the dialog box in Figure 13-4.

19 454060-ch13.indd 25219 454060-ch13.indd 252 4/21/09 7:33:09 PM4/21/09 7:33:09 PM

253

Chapter 13: Slightly More Complicated Testing

4. In the Input Range box, type the cell range that holds all the data.

For the example, the data are in $B$2:$D$10. Note the $-signs for abso-

lute referencing. Note also — again, this is important — the labels for

the row factor (presentation method) are part of the data range. The

labels for the column factor are part of the range, too. The first cell in

the range, B2, is blank, but that’s OK.

5. If the cell ranges include column headings, select the Labels option.

I included the headings in the ranges, so I checked the box.

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

different α.

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

want the results.

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

worksheet.

8. Click OK.

Because I selected New Worksheet Ply, a newly created page opens with

the results.

Figure 13-5 shows the tool’s output, after I expanded the columns. The output

features two tables, SUMMARY and ANOVA.

The SUMMARY table is in two parts. The first part provides summary statis-

tics for the factor combinations and for the row factor. The second part pro-

vides summary statistics for the column factor. Summary statistics include

the number of scores in each row-column combination, in each row, and in

each column along with the counts, sums, means, and variances.

The ANOVA table presents the Sums of Squares, df, Mean Squares, F,

P-values, and critical F for the indicated df. The table features three values

for F. One F is for the row factor, one for the column factor, and one for the

interaction. In the table, the row factor is called Sample. The P-value is the

proportion of area that the F cuts off in the upper tail of the F-distribution.

If this value is less than .05, reject H

In this example, the decisions are to reject H

for the Presentation Method

(the row factor, labeled Sample in the table), to not reject H

for the

Presentation Style (the column factor), and to reject H

for the interaction.

19 454060-ch13.indd 25319 454060-ch13.indd 253 4/21/09 7:33:09 PM4/21/09 7:33:09 PM

254

Part III: Drawing Conclusions from Data

Figure 13-5:

Output from

the Anova:

Two Factor

Without

Replication

data analy-

sis tool.

19 454060-ch13.indd 25419 454060-ch13.indd 254 4/21/09 7:33:09 PM4/21/09 7:33:09 PM

Chapter 14

Regression: Linear and Multiple

In This Chapter

▶ Summarizing a relationship

▶ Working with regression

▶ Hypothesis testing and regression

▶ Balancing many relationships

ne of the main things you do when you work with statistics is make

predictions. The idea is to take data on one or more variables, and

use these data to predict a value of another variable. To do this, you have

to understand how to summarize relationships among variables, and to test

hypotheses about those relationships.

In this chapter, I introduce regression, a statistical way to do just that.

Regression also enables you to use the details of relationships to make pre-

dictions. First, I show you how to analyze the relationship between one vari-

able and another. Then I show you how to analyze the relationship between a

variable and two others. These analyses involve a good bit of calculation, and

Excel is more than equal to the task.

The Plot of Scatter

Sahutsket University is an exciting, dynamic institution. Every year, the

school receives thousands of applications. One challenge the Admissions

Office faces is this: Applicants want the Office to predict what their GPAs

(grade-point averages on a 4.0 scale) will be if they attend Sahutsket.

What’s the best prediction? Without knowing anything about an applicant,

and only knowing its own students’ GPAs, the answer is clear: It’s the aver-

age GPA at Sahutsket U. Regardless of who the applicant is, that’s all the

Admissions Office can say if its knowledge is limited.

20 454060-ch14.indd 25520 454060-ch14.indd 255 4/21/09 7:33:50 PM4/21/09 7:33:50 PM

256

Part III: Drawing Conclusions from Data

With more knowledge about the students and about the applicants, a more

accurate prediction becomes possible. For example, if Sahutsket keeps

records on its students’ SAT scores (Verbal and Math combined), the

Admissions Office can match up each student’s GPA with his or her SAT

score and see if the two pieces of data are somehow related. If they are,

an applicant can supply his or her SAT score, and the Admissions Office

can use that score to help make a prediction.

Figure 14-1 shows the GPA-SAT matchup in a graphic way. Because the points

are scattered, it’s called a scatterplot. By convention, the vertical axis (the

y-axis) represents what you’re trying to predict. That’s also called the depen-

dent variable or the y-variable. In this case, that’s GPA. Also by convention,

the horizontal axis (the x-axis) represents what you’re using to make your

prediction. That’s also called the independent variable or the x-variable. Here,

that’s SAT.

Figure 14-1:

SATs and

GAPs in the

Sahutsket

University

student

body.

SAT

GPA

900 1100 1300 1500

4.0

3.5

3.0

2.5

2.0

1.5

Average GPA

SAT =1000

GPA = 2.5

Each point in the graph represents an individual student’s GPA and SAT. In

a real scatterplot of a university student body, you’d see many more points

than I show here. The general tendency of the set of points seems to be that

high SAT scores are associated with high GPAs and low SAT scores are asso-

ciated with low GPAs.

I singled out one of the points. It shows a Sahutsket student with an SAT

score of 1000 and a GPA of 2.5. I also show the average GPA to give you a

sense that knowing the GPA-SAT relationship provides an advantage over

just knowing the mean.

20 454060-ch14.indd 25620 454060-ch14.indd 256 4/21/09 7:33:51 PM4/21/09 7:33:51 PM

257

Chapter 14: Regression: Linear and Multiple

How do you make that advantage work for you? You start by summarizing

the relationship between SAT and GPA. The summary is a line through the

points. How and where do you draw the line?

I’ll get to that in a minute. First, I have to tell you about lines in general.

Graphing Lines

In the world of mathematics, a line is a way to picture a relationship between

an independent variable (x) and a dependent variable (y). In this relationship

every time I supply a value for x, I can figure out the corresponding value for y.

The equation says to take the x-value, multiply by 2, and then add 3.

If x = 1, for example, y = 6. If x =2, y = 8. Table 14-1 shows a number of x-y pairs

in this relationship, including the pair in which x = 0.

Table 14-1 x-y Pairs in y = 4+ 2x

310

412

514

616

Figure 14-2 shows these pairs as points on a set of x-y axes, along with a line

through the points. Each time I list an x-y pair in parentheses, the x-value is

first.

As the Figure shows, the points fall neatly onto the line. The line graphs the

equation y = 4 + 2x. In fact, whenever you have an equation like this, where x

isn’t squared or cubed or raised to any power higher than 1, you have what

mathematicians call a linear equation. (If x is raised to a higher power than

one, you connect the points with a curve, not a line.)

20 454060-ch14.indd 25720 454060-ch14.indd 257 4/21/09 7:33:51 PM4/21/09 7:33:51 PM

258

Part III: Drawing Conclusions from Data

Figure 14-2:

The graph

for

y = 4 + 2x.

0123456

(0,4)

(1,6)

(2,8)

(3,10)

(4,12)

(5,14)

(6,16)

A couple of things to keep in mind about a line: You can describe a line in

terms of how slanted it is, and where it runs into the y-axis.

The how-slanted-it-is part is called the slope. The slope tells you how much y

changes when x changes by 1 unit. In the line in Figure 14-2, when x changes

by 1 (from 4 to 5, for example) y changes by 2 (from 12 to 14).

The where-it-runs-into-the-y-axis part is called the y-intercept (or sometimes

just the intercept). That’s the value of y when x = 0. In Figure 14-2 the y-inter-

cept is 4.

You can see these numbers in the equation. The slope is the number that

multiplies x and the intercept is the number you add to x. In general,

where a represents the intercept and b represents the slope.

The slope can be a positive number, a negative number, or zero. In Figure 14-2,

the slope is positive. If the slope is negative, the line is slanted in a direc-

tion opposite to what you see in Figure 14-2. A negative slope means that

y decreases as x increases. If the slope is zero, the line is parallel to the hori-

zontal axis. If the slope is zero, y doesn’t change as x changes.

The same applies to the intercept — it can be a positive number, a negative

number, or zero. If the intercept is positive, the line cuts off the y-axis above

the x-axis. If the intercept is negative, the line cuts off the y-axis below the

20 454060-ch14.indd 25820 454060-ch14.indd 258 4/21/09 7:33:51 PM4/21/09 7:33:51 PM