Schmuller J. Statistical Analysis with Excel For Dummies

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

229

Chapter 12: Testing More Than Two Samples

The steps for using this tool are:

1. Enter the data for each sample into a separate data array.

For this example the data in the Method 1 sample are in column B,

the data in the Method 2 sample are in Column C, and the data for the

Method 3 sample are in column D.

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: Single Factor. Click OK to open the Anova: Single

Factor dialog box.

This is the dialog box in Figure 12-2.

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

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

lute referencing.)

5. If the cell ranges include column headings, check the Labels checkbox.

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’re so

inclined.

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 12-3 shows the tool’s output, after I expanded the columns. The output

features two tables, SUMMARY and ANOVA. The SUMMARY table provides

summary statistics of the samples — the number in each group, the group

sums, averages, and variances. The ANOVA table presents the Sums of

Squares, df, Mean Squares, F, P-value, and critical F for the indicated df. 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

18 454060-ch12.indd 22918 454060-ch12.indd 229 4/21/09 7:31:37 PM4/21/09 7:31:37 PM

230

Part III: Drawing Conclusions from Data

Figure 12-3:

Output from

the Anova:

Single

Factor anal-

ysis tool.

Comparing the means

Excel’s ANOVA tool does not provide a built-in facility for carrying out

planned (or unplanned) comparisons among the means. With a little ingenu-

ity, however, you can use the Excel worksheet function SUMPRODUCT to do

those comparisons.

The worksheet page with the ANOVA output is the launching pad for the

planned comparisons. In this section, I take you through one planned

comparison — the mean of Method 1 versus the mean of Method 2.

Begin by creating columns that hold important information for the compari-

sons. Figure 12-4 shows what I mean. I put the comparison coefficients in

column J, the squares of those coefficients in column K, and the reciprocal of

each sample size (1/n) in column L.

Figure 12-4:

Carrying out

a planned

comparison.

18 454060-ch12.indd 23018 454060-ch12.indd 230 4/21/09 7:31:37 PM4/21/09 7:31:37 PM

231

Chapter 12: Testing More Than Two Samples

A few rows below those cells, I put t-test related information — the t-test

numerator, the denominator, and the value of t. I use separate cells for the

numerator and denominator to simplify the formulas. You can put them

together in one big formula and just have a cell for t, but it’s hard to keep

track of everything.

SUMPRODUCT takes arrays of cells, multiplies the numbers in the corre-

sponding cells, and sums the products. (This function is in the Math & Trig

Functions menu, not the Statistical Functions menu.) I used SUMPRODUCT to

multiply each coefficient by each sample mean and then add the products.

I stored that result in K11. That’s the numerator for the planned comparison

t-test. The formula for K11 is

=SUMPRODUCT(J5:J7,D5:D7)

The array J5:J7 holds the comparison coefficients, and D5:D7 holds the

sample means.

K12 holds the denominator. I selected K12 so you could see its formula in the

formula bar:

=SQRT(D13*(SUMPRODUCT(K5:K7,L5:L7)))

D13 has the MS

. SUMPRODUCT multiplies the squared coefficients in K5:K7

by the reciprocals of the sample sizes in L5:L7 and sums the products. SQRT

takes the square root of the whole thing.

K13 holds the value for t. That’s just K11 divided by K12.

K14 presents the P-value for t — the proportion of area that t cuts off in the

upper tail of the t-distribution with df = 24. The formula for that cell is

=TDIST(K13,C13,1)

The arguments are the calculated t (in K13), the degrees of freedom for MS

(in C13), and the number of tails.

If you change the coefficients in J5:J7, you instantaneously create and com-

plete another comparison.

In fact, I’ll do that right now, and show you Scheffé’s post hoc comparison.

That one, in this example, compares the mean of Method 1 with the mean

of Method 3. Figure 12-5 shows the extra information for this test, starting a

couple of rows below the t-test.

18 454060-ch12.indd 23118 454060-ch12.indd 231 4/21/09 7:31:37 PM4/21/09 7:31:37 PM

232

Part III: Drawing Conclusions from Data

Figure 12-5:

Carrying out

a post hoc

comparison.

Cell K16 holds F, the square of the t value in K13. K17 has F’, the product of

C12 (df

, which is the number of samples – 1) and G12 (the critical value of

F for 2 and 24 degrees of freedom and α = .05). K16 is greater than K17, so

reject H

for this comparison.

Another Kind of Hypothesis,

Another Kind of Test

The ANOVA I just showed you works with independent samples. As you may

remember from Chapter 11, sometimes you work with matched samples. For

example, sometimes a person provides data in a number of different condi-

tions. In this section, I introduce the ANOVA you use when you have more

than two matched samples.

This type of ANOVA is called repeated measures. You’ll see it called other

names, too, like randomized blocks or within subjects.

Working with repeated measures ANOVA

To show how this works, I extend the example from Chapter 11. In that exam-

ple, ten people participate in a weight-loss program. Table 12-3 shows their

data over a three-month period.

18 454060-ch12.indd 23218 454060-ch12.indd 232 4/21/09 7:31:37 PM4/21/09 7:31:37 PM

233

Chapter 12: Testing More Than Two Samples

Table 12-3 Data for the Weight-Loss Example

Person Before One Month Two

Months

Three

Months

Mean

1 198 194 191 188 192.75

2 201 203 200 196 200.00

3 210 200 192 188 197.50

4 185 183 180 178 181.50

5 204 200 195 191 197.50

6 156 153 150 145 151.00

7 167 166 167 166 166.50

8 197 197 195 192 195.25

9 220 215 209 205 212.25

10 186 184 179 175 181.00

Mean 192.4 189.5 185.8 182.4 187.525

Is the program effective? This question calls for a hypothesis test:

: μ

Before

= μ

: Not H

Once again, I set α = .05

As in the previous ANOVA, start with the variances in the data. The MS

the variance in all 40 scores from the grand mean, which is 187.525:

The people participating in the weight-loss program also supply variance.

Each one’s overall mean (his or her average over the four measurements)

varies from the grand mean. Because these data are in the rows, I call this

Rows

18 454060-ch12.indd 23318 454060-ch12.indd 233 4/21/09 7:31:38 PM4/21/09 7:31:38 PM

234

Part III: Drawing Conclusions from Data

The means of the columns also vary from the grand mean:

One more source of variance is in the data. Think of it as the variance left

over after you pull out the variance in the rows and the variance in the col-

umns from the total variance. Actually, it’s more correct to say that it’s the

Sum of Squares left over when you subtract the SS

Rows

and the SS

Columns

from

the SS

This variance is called MS

Error

. As I say earlier, in the ANOVA the denomina-

tor of an F is called an “error term.” So the word “error” here gives you a hint

that this MS is a denominator for an F.

To calculate MS

Error

, you use the relationships among the Sums of Squares

and among the df.

Here’s another way to calculate the df

Error

To perform the hypothesis test, you calculate the F:

With 3 and 27 degrees of freedom, the critical F for α = .05 is 2.96. (Look it up

or use the Excel worksheet function FINV.) The calculated F is larger than the

critical F, so the decision is to reject H

What about an F involving MS

Rows

? That one doesn’t figure into H

for this

example. If you find a significant F, all it shows is that people are different

from one another with respect to weight and that doesn’t tell you very much.

As is the case with the ANOVA I showed you before, you plan comparisons to

zero in on the differences. You can use the same formula, except you substi-

tute MS

Error

for MS

18 454060-ch12.indd 23418 454060-ch12.indd 234 4/21/09 7:31:38 PM4/21/09 7:31:38 PM

235

Chapter 12: Testing More Than Two Samples

The df for this test is df

Error

For Scheffé’s post hoc test, you also follow the same procedure as before and

substitute MS

Error

for MS

. The only other change is to substitute df

Columns

for

and substitute df

Error

for df

when you find F’.

Getting trendy

In situations like the one in the weight-loss example, you have an inde-

pendent variable that’s quantitative — its levels are numbers (0 months,

1 month, 2 months, 3 months). Not only that, but in this case, the intervals

are equal.

With that kind of an independent variable, it’s often a good idea to look for

trends in the data, rather than just plan comparisons among means. If you

graph the means in the weight-loss example, they seem to approximate a line,

as Figure 12-6 shows. Trend analysis is the statistical procedure that exam-

ines that pattern. The objective is to see if the pattern contributes to the sig-

nificant differences among the means.

Figure 12-6:

The means

for the

weight-loss

example.

178

180

182

184

186

188

190

192

194

Before

Time

1 Month 2 Months 3 Months

Weight (lbs)

A trend can be linear, as it apparently is in this example, or nonlinear (in which

the means fall on a curve). In this example, I only deal with linear trend.

To analyze a trend, you use comparison coefficients — those numbers you

use in planned comparisons. You use them in a slightly different way than

you did before.

18 454060-ch12.indd 23518 454060-ch12.indd 235 4/21/09 7:31:38 PM4/21/09 7:31:38 PM

236

Part III: Drawing Conclusions from Data

Here, you use comparison coefficients to find a Sum of Squares for linear

trend. I abbreviate that as SS

Linear

. This is a portion of SS

Columns

. In fact,

Also,

After you calculate SS

Linear

, you divide it by df

Linear

to produce MS

Linear

. This is

extremely easy because df

Linear

= 1. Divide MS

Linear

by MS

Error

and you have an

F. If that F is higher than the critical value of F with df = 1 and df

Error

at your α

–level, then weight is decreasing in a linear way over the time period of the

weight-loss program.

The comparison coefficients are different for different numbers of samples.

For four samples, the coefficients are –3, –1, 1, and 3. To form the SS

Linear

the

formula is

In this formula, n is the number of people and c represents the coefficients.

Applying the formula to this example,

This is such a large proportion of SS

Columns

that SS

Nonlinear

is really small:

As I pointed out before, df = 1, so MS

Linear

is conveniently the same as SS

Linear

Finally,

The critical value for F with 1 and 27 degrees of freedom and α = .05 is 4.21.

Because the calculated value is larger than the critical value, statisticians

would say the data shows a significant linear component. This, of course, veri-

fies what you see in Figure 12-6.

18 454060-ch12.indd 23618 454060-ch12.indd 236 4/21/09 7:31:38 PM4/21/09 7:31:38 PM

237

Chapter 12: Testing More Than Two Samples

A little more on trend

The coefficients I showed you represent

one possible component of what underlies

the differences among the four means in the

example — the linear component. With four

means, it’s also possible to have other com-

ponents. I lumped those other components

together into a category I called nonlinear. Now

I discuss them explicitly.

One possibility is that four means can differ

from one another and form a trend that looks

like a curve, as in the next figure.

(continued)

178

180

182

184

186

188

190

192

194

Before

Time

1 Month 2 Months 3 Months

Weight (lbs)

178

180

182

184

186

188

190

192

194

Before 1 Month 2 Months 3 Months

Weight (lbs)

Time

Four means can form still another kind of trend:

18 454060-ch12.indd 23718 454060-ch12.indd 237 4/21/09 7:31:39 PM4/21/09 7:31:39 PM

238

Part III: Drawing Conclusions from Data

Data analysis tool: Anova: Two Factor

Without Replication

Huh? Is that a misprint? Two-Factor??? Without Replication?? What’s that all

about?

Here’s the story: If you’re looking through the data analysis tools for some-

thing like Anova: Single Factor Repeated Measures, you won’t find it. The tool

you’re looking for is there, but it’s hiding out under a different name.

Figure 12-7 shows this tool’s dialog box along with the data for the preceding

weight-loss example.

(continued)

The first kind, where the trend changes direc-

tion once is called a quadratic component. In the

first figure it increases, and then it decreases.

The second, where the trend changes direc-

tion twice is called a cubic component. In the

second figure it increases, decreases, and

then increases again. In Figure 12-6, the trend

is linear and doesn’t change direction (it just

keeps decreasing).

Quadratic and cubic components have coeffi-

cients, too, and here they are:

Quadratic: 1, –1, –1, 1

Cubic: –1, 3, –3, 1

You test for these components the same way

you test for the linear component. A trend can

be a combination of components: If you have a

significant F, one or more of these trend compo-

nents might be significant.

Linear, quadratic, and cubic are as far as you

can go with four means. With five means, you

can look for those three plus a quartic compo-

nent (three direction-changes), and with six you

can try and scope out all of the preceding plus

a quintic component (four direction-changes).

What do the coefficients look like?

For five means, they’re:

Linear: –2, –1, 0 , 1, 2

Quadratic: 2, –1, –2, –1, 2

Cubic: -1, 2, 0, –2, 1

Quartic: 1, –4, 6, –4, 1

And for six means:

Linear: –5, –3, –1 , 1, 3, 5

Quadratic: 5, –1, –4, –4, –1, 5

Cubic: –5, 7, 4, –4, –7, 5

Quartic: 1, –3, 2, 2, –3, 1

Quintic: –1, 5, –10, 10, –5, 1

I could go on with more means, coefficents,

and exotic component names (hextic? septic?),

but enough already. This should hold you for a

while.

18 454060-ch12.indd 23818 454060-ch12.indd 238 4/21/09 7:31:39 PM4/21/09 7:31:39 PM