Paired ttest Using Microsoft
Excel
These tutorials briefly explain the use
and interpretation of standard statistical analysis techniques. The
examples include howto instructions for Excel. Although there are
different version of Excel in use, these should work about the same for
most recent versions. They also assume that you have installed the Excel
Analysis Pak which is free and comes with Excel (Go to Tools,
Addins... if it is not already installed in your version of Excel.)
See
www.stattutorials.com/EXCELDATA
for files mentioned in this tutorial,
© TexaSoft, 2008
To compare
two paired values (such as in a beforeafter situation) where both
observations are taken from the same or matched subjects, you can perform
a paired t For example, suppose your data contained the variables BEFORE
and AFTER, (before and after weight on a diet), for 8 subjects. The
hypotheses for this test are:
_{Ho: mLoss = 0 (The average weight loss was 0)}
Ha: mLoss ≠ 0 (The weight
loss was different than 0)
For example,
the following weight loss data is used in this example
(DIET.XLS)
Before 
After 
162.00 
168.00 
170.00 
136.00 
184.00 
147.00 
164.00 
159.00 
172.00 
143.00 
176.00 
161.00 
159.00 
143.00 
170.00 
145.00 
1. To
perform a paired ttest, select Tools/ Data Analysis / ttest: Paired
two sample for means.
2. In the
ttest: Paired two sample for means dialog box: For the Input Range
for Variable 1, highlight the 8 values of Score in group “Before” (values
from 162 to 170). For the input range for Variable 2, highlight the eight
values of Score in group “After” (values from 168 to 145). For now, leave
the other items at their default selections. This dialog box is shown
below. Click OK. This dialog box is shown below:
3. The
results are shown in the output below:
tTest: Paired Two Sample for Means 






Variable 1 
Variable 2 
Mean 
169.625 
150.25 
Variance 
65.125 
121.9285714 
Observations 
8 
8 
Pearson Correlation 
0.176747772 

Hypothesized Mean Difference 
0 

df 
7 

t Stat 
3.706873373 

P(T<=t) onetail 
0.003792994 

t Critical onetail 
1.894578604 

P(T<=t) twotail 
0.007585988 

t Critical twotail 
2.364624251 

Thus, the twotail pvalue for this
ttest is p=0.008 (.007585988) and t=3.71.
Excel actually
does a poor job providing what you need to report the results of this test
– for a more complete understanding, you need to realize that the paired
ttest is actually a test on the DIFFERENCE between the two values. Thus,
to make this a better analysis, first calculate the difference between
BEFORE and AFTER, creating the following new column called “DIFF” using a
formula such as =A2B2 in cell C2 and copying the formula for the
appropriate remaining cells in the worksheet. Notice also that the average
difference is calculated (19.38)
Before 
After 
DIFF 
162.00 
168.00 
6.00 
170.00 
136.00 
34.00 
184.00 
147.00 
37.00 
164.00 
159.00 
5.00 
172.00 
143.00 
29.00 
176.00 
161.00 
15.00 
159.00 
143.00 
16.00 
170.00 
145.00 
25.00 
Average Diff= 
19.38 
Look back up
at the original hypotheses – what you are testing is that the average loss
is different than zero (0). Thus, the ttest is actually testing to
determine if the value 19.38 is sufficiently different from 0 to claim
significance. Thus, the number you are interested in most is the average
difference (loss) and not as much as the individual means of Before and
After.
Therefore to
report these results properly, you need the mean difference and standard
deviation. You can get this be calculating descriptive statistics on the
difference values. (Tools/Data Analysis/ Descriptive Statistics) –
choose the Summary Statistics and 95% confidence interval options. The
results in the following output:
Column1 


Mean 
19.375 
Standard Error 
5.226776868 
Median 
20.5 
Mode 
#N/A 
Standard Deviation 
14.78355747 
Sample Variance 
218.5535714 
Kurtosis 
0.52419581 
Skewness 
0.575291944 
Range 
43 
Minimum 
6 
Maximum 
37 
Sum 
155 
Count 
8 
Confidence Level(95.0%) 
12.35936334 
Notice that
the mean divided by the standard error (19.375/5.227 = 3.71) is same as
the value of the “t Stat” in the previous table. Another piece of
information that is usually reported is a 95% confidence interval. Using
the Confidence Level (95%) value of 12.359 in the table, the confidence
interval is the mean plus or minus this value. Thus, a 95% C.I. about Mean
Difference is (7.01, 31.74).
To report
these results in a journal article, you could use something like this:
“A paired ttest was performed to determine if the diet was effective.
The mean weight loss (M=19.38, SD =14.784, N= 8) was significantly greater
than zero, t(7)=3.71, twotail p = 0.008, providing evidence that the
diet is effective in producing weight loss. A 95% C.I. about mean weight
loss is (7.01, 31.74).”
NOTE: The
researcher should interpret the results using his or her knowledge of the
subject matter – thus giving the variability of the sample, the weight
loss could have been as low as an average of 7 pound to a high of 32
pounds (see the 95% confidence interval). If it is as low as 7 pounds,
would this still mean that the diet was effective (in terms of the
researcher’s experience.)?
NOTE: The test
could have also been performed as a onetail test. If so, use the
appropriate tstatistic and pvalue from the Excel table.
NOTE: Also,
you could do this test using an hypothesized value of the difference other
than zero – although zero is almost always used. Excel provides the
opportunity to enter another hypothesized value to test in the paired
ttest dialog box.
End of
tutorial
See
http://www.stattutorials.com/EXCEL