 |
Statistics Tutorials
for
Statistical
Data Analysis:
SAS, SPSS, WINKS, Excel
For more professional statistical results:
WINKS Statistical Software
Affordable. Reliable. Relevant.
Analyze with WINKS.
www.texasoft.com
Paired t-test Using Microsoft Excel
These tutorials briefly explain the use and interpretation of standard statistical analysis techniques. The examples include how-to 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
Performing a Paired t-test in Excel
To compare two paired values (such as in a before-after 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 t-test, select Tools/ Data Analysis / t-test: Paired two sample for means.
2. In the t-test: 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:
t-Test: 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) one-tail
0.003792994
t Critical one-tail
1.894578604
P(T<=t) two-tail
0.007585988
t Critical two-tail
2.364624251
Thus, the two-tail p-value for this t-test 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 t-test 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 =A2-B2 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)
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 t-test 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 t-test 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, two-tail 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 one-tail test. If so, use the appropriate t-statistic and p-value 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 t-test dialog box.
End of tutorial
See http://www.stattutorials.com/EXCEL
For more information... we recommend:
- WINKS Software-- a simple, inexpensive alternative to Excel -- easy to use, reads Excel file, and provides much more explanation for results -- to use and affordable statistical software program that will help you analyze, interpret and write-up your results. Download a free trial copy. WINKS reads Excel files, so you can read your Excel data into WINKS and do many more types of analysis than in Excel alone.
- SAS Essentials: Mastering SAS for Research
- provides an introduction to SAS statistical software, the premiere statistical data analysis tool for scientific research. Through its straightforward approach, the text presents SAS with step-by-step examples. SAS Essentials introduces a step-by-step approach to mastering SAS software for statistical data analysis. It's also a valuable reference tool for any researcher currently using SAS. Designed for those new to SAS and filled with illustrative examples, the book shows how to read, write and import data; prepare data for analysis; use SAS procedures; evaluate quantitative data; analyze counts and crosstabulation tables; and compare means using the t-test. The book also provides instruction and examples on analysis of variance, correlation and regression, nonparametric analysis, logistic regression, creating graphs, controlling outputs using ODS, as well as advanced topics in SAS programming.ISBN: 0470461292. Order from publisher -Jossey-Bass/Wiley. Barnes & Noble. Amazon.
- Against All Odds VIDEOS - Now in DVD format -- Teaching Videos from Annenberg/PBS --"This highly engaging primer on statistical methods and inference introduces the practical applications of statistics. Produced by the Consortium for Mathematics and Its Applications and Chedd-Angier." Click here for info
- BeSmartNotes Reference sheets for SAS, SAS ODS, SAS Functions, SPSS and WINKS - Click here for info. (www.besmartnotes.com)
- Statistical Analysis Quick Reference Guidebook: With SPSS Examples is a practical "cut to the chase" handbook that quickly explains the when, where, and how of statistical data analysis as it is used for real-world decision-making in a wide variety of disciplines. It contains examples using SPSS Statistics software. In this one-stop reference, the authors provide succinct guidelines for performing an analysis, avoiding pitfalls, interpreting results, and reporting outcomes. Paperback. Sage Publishers ISBN: 1412925606 Order book from Amazon
| Send comments | Back to Tutorial Menu | TexaSoft |