First of all, check to make sure that the Analysis ToolPak is installed. Here is how you do it:
a. From the Tools menu, choose Add-Ins
b. Make sure Analysis ToolPak is checked, click on okay
Using a t-Test: Paired Two Sample for Means to calculate differences between two groups:
1. This analysis tool and its formula perform a paired two-sample t-test to determine whether a sample’s means are distinct. You can use this test when there is a natural pairing of observations in the
samples, such as when a sample group is tested twice – before and after an experiment.
2. Type in the chart below:
A B
1 Grp A Grp B
2 8 5
3 7 5
4 9 8
5 8 10
6 9 6
7 4 7
8 6 4
9 5 7
10 9 7
11 8 7
12 5 9
13 5 10
14 9 8
15 9 5
16 7 6
17 8 7
18 8 9
19 7 8
20 6 4
3. From the Tools Menu, choose Data Analysis
4. Scroll down until you see t-Test: Paired Two Sample for Means; highlight it, then click on OK 5. A dialog box appears.
a. For the Variable 1 Range, use your mouse and highlight the cells containing the data for column one [A-1 to A-20].
b. For the Variable 2 Range, use your mouse and highlight the cells containing the data for column one [B-1 to B-20].
c. For the Hypothesized Mean Difference, type in 0 [zero]
d. Check the Labels box
e. Check the New Worksheet Ply and type in the name Pre-Post
6. A new worksheet has been inserted into your document. Use the Decrease Decimals function to make all numbers only two (2) decimal places. Your t-Test: Paired Two Sample for Means worksheet should look like this:
t-Test: Paired Two Sample for Means
Grp A Grp B
Mean 7.20 7.00
Variance 2.48 3.26
Observations 20.00 20.00
Pearson Correlation -0.06
Hypothesized Mean Difference 0.00
df 19.00
t Stat 0.36
P(T<=t) one-tail 0.36
t Critical one-tail 1.73
P(T<=t) two-tail 0.72
t Critical two-tail 2.09
7. To interpret the chart, first look at the mean [or average] score. The mean for Grp A is 7.20 and for Grp B is 7.00. Next look at the p(T<=t) two tail, which shows the p-value to be .72. That means the two columns are not significantly different [remember, p should be equal to or less than .05.
Using an ANOVA: Two-Factor With Replication to calculate differences between two groups on two tests:
1. This analysis tool and its formula perform tests the significance of each of the two variables [group (A vs. B) and time (pretest vs posttest). This is a simple repeated measures ANOVA
2. Type in the chart on the next page:
A B C
1 Grp A Grp B
2 Pretest 8 5
3 7 5
4 9 8
5 8 10
6 9 6
7 4 7
8 6 4
9 5 7
10 9 7
11 8 7
12 5 9
13 5 10
14 9 8
15 9 5
16 7 6
17 8 7
18 8 9
19 7 8
20 6 4
21 7 8
22 Posttest 15 25
23 15 21
24 15 21
25 10 23
26 8 16
27 13 24
28 14 19
29 13 23
30 10 19
31 13 22
32 10 21
33 9 15
34 11 18
35 9 24
36 15 16
37 13 16
38 14 16
39 8 19
40 10 15
41 9 17
3. From the Tools Menu, choose Data Analysis
4. Scroll down until you see ANOVA: Two Factor With Replication; highlight it, then click on OK
a. For the Input Range, use your mouse and highlight the cells containing the data for column one [A- 1 to C-41].
b. For the Rows per sample, type in 20 because there are twenty pretest scores per group and twenty posttest scores per group. It is VERY important that you have equal numbers of pretest and posttest scores.
c. Check the New Worksheet Ply and type in the name ANOVA Pre-Post d. Check OK
6. A new worksheet has been inserted into your document. Use the Decrease Decimals function to make all numbers only two (2) decimal places. Your ANOVA: Two Factor With Replication worksheet should look like this:
Anova: Two-Factor With Replication
SUMMARY Grp A Grp B Total
Pretest
Count 20 20 40
Sum 144 140 284
Average 7.20 7.00 7.10
Variance 2.48 3.26 2.81
Posttest
Count 20 20 40
Sum 234 390 624
Average 11.70 19.50 15.60
Variance 6.43 10.89 24.04
Total
Count 40 40
Sum 378 530
Average 9.45 13.25
Variance 9.54 46.96
ANOVA
Source of Variation SS df MS F P-value F crit
Sample 1445 1 1445.00 250.50 0.0000 3.97
Columns 288.80 1 288.80 50.07 0.0000 3.97
Interaction 320 1 320.00 55.47 0.0000 3.97
Within 438.40 76 5.77
Total 2492.20 79
7. To interpret the above chart, first look at the individual average [mean] scores in the Pretest
Summary. The Pretests mean for Grp A is 7.20 and for Grp B is 7.00. Next, look at the average [mean]
score in the Posttest Summary. The Posttests mean for Grp A is 11.70 and for Grp B is 19.50 8. Finally, look at the ANOVA table. The p-value for sample [pretest versus posttest] is significant
because p<.05. The p-value for columns [Grp A vs Grp B] is significant because p<.05. The last [and most important] p-value to analyze is the p-value for interaction. The interaction [time
(pretest/posttest) by group (Grp A vs. Grp B)] is significant because p<.05.
Graphing the results of an ANOVA: Two-Factor With Replication
1. This allows you to present statistical data in a graphic, easy to understandable way
2. Type in the chart below [this information came from the above summary chart of mean (average) scores].
A B C
1 Grp A Grp B
2 Pre 7.20 7.00
3 Post 11.84 19.63
3. Highlight cells A1 to C3 and from the Insert Menu, choose Chart 4. From the Chart Type Menu, choose Column, then click on NEXT 5. Click on NEXT for the Data Range screen.
6. From the NEXT menu, type in the chart title of Pretest - Posttest, then click on NEXT
7. From the Place Chart Menu choose As a New Sheet [it is a very important to choose new sheet]. Type in the chart name as Chart Pretest/Posttest and then click on NEXT
8. A new worksheet has been inserted into your document that should look like the chart below. You can cut and paste the various statistics and charts you have created into a word processor document and have professional looking charts and numbers in your program evaluation.
Pretest - Posttest
0.00 5.00 10.00 15.00 20.00 25.00
Grp A Grp B
Pre Post
