 |
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
Two Sample (independent group) 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
When the means of two groups are to be compared (where each group consists of subjects that are not related) then the Excel two-sample t-test procedure is used to perform these calculations.
(NOTE: If your observations are related across “group” as paired or repeated measurements, this in an INCORRECT version of the t-test. For that case, see the tutorial on the Paired t-test.)
Assumptions: Subjects are randomly assigned to one of two groups. The distribution of the means by group is normal with equal variances. Sample sizes between groups do not have to be equal.
Test: The hypotheses for the comparison of means from two independent groups are:
Ho: m1 = m2 (means of the two groups are equal)Ha: m1 ¹ m2 (means are not equal)
The test statistic is a student’s t-test with N‑2 degrees of freedom, where N is the total number of subjects. A low p‑value indicates evidence to reject the null hypothesis in favor of the alternative. In other words, there is evidence that the means are not equal.
For example, suppose we are interested in comparing SCORES across GROUPS, where there are two groups. The purpose is to determine if the mean SCORE on a test is different for the two groups tested (i.e., control and treatment groups). The example data is shown here:
|
Group |
Scores |
|
1 |
20 |
|
1 |
23 |
|
1 |
32 |
|
1 |
24 |
|
1 |
25 |
|
1 |
28 |
|
1 |
27.5 |
|
2 |
25 |
|
2 |
46 |
|
2 |
56 |
|
2 |
45 |
|
2 |
46 |
|
2 |
51 |
|
2 |
34 |
|
2 |
47.5 |
In this example, GROUP contains two values, 1 or 2, indicating which group each subject was in. The t-test will be performed on the values in the variable (column) named SCORE.
An independent group t-test is done in two steps:
Step 1: Decide if the variances are equal in both groups, which determines the type of t-test to perform (one that assumes equal variances or one that doesn’t make that assumption.) A conservative approach suggested in some texts is to always assume unequal variances. Another approach is to do a statistical test to determine equality.
Step 2: Depending on you decision about the equality of variances you either perform the version of the t-test that assumes equality of variances or other one that doesn’t make that assumption.
Determine Equality of Variance
If you take the conservative approach, skip this test and proceed to the version of the t-test that does not assume equality of variance.
To do a statistical test to determine equality of variance, follow these instructions. (The test for equality of variances is an F-test.)
1. In Excel, select Tools/ Data Analysis / F-Test Two Sample for Variance.
2. In the F-Test Two Sample for Variance dialog box: For the Input Range for Variable 1, highlight the seven values of Score in group 1 (values from 20 to 27.5). For the input range for Variable 2, highlight the eight values of Score in group 2 (values from 25 to 47.5). Leave the other items at their default selections. This dialog box is shown below. Click OK.
3. The following results are produced by Excel:
|
F-Test Two-Sample for Variances |
|
|
|
|
|
|
|
|
Variable 1 |
Variable 2 |
|
Mean |
25.64285714 |
43.8125 |
|
Variance |
15.22619048 |
96.42410714 |
|
Observations |
7 |
8 |
|
df |
6 |
7 |
|
F |
0.157908545 |
|
|
P(F<=f) one-tail |
0.019378053 |
|
|
F Critical one-tail |
0.23771837 |
|
Notice the highlighted probability p=0.01937. This is a one-tail p-value associated with the test for equality of variance. Generally, if this value is less than 0.05 you assume that the variances are NOT equal.
a. If the variances are assumed to NOT be equal, proceed with the t-test that assumes non-equal variances.
b. If the variances are assumed to be equal, proceed with the t-test that assumes equal variances.
Perform the t-test
The process of doing the t-test in Excel is similar for both the equal and unequal variances case – the main difference is which version you select from the menu. Suppose you select the unequal version of the two-sample t-test – this is how you proceed:
1. Select Tools/ Data Analysis/ t-Test: Two Sample assuming Unequal Variances
2. For the Input Range for Variable 1, highlight the seven values of Score in group 1 (values from 20 to 27.5). For the input range for Variable 2, highlight the eight values of Score in group 2 (values from 25 to 47.5). Leave the other items at their default selections. This dialog box is shown below. Click OK.
3. The following output is created:
|
t-Test: Two-Sample Assuming Unequal Variances |
|
|
|
|
|
|
|
|
Variable 1 |
Variable 2 |
|
Mean |
25.64285714 |
43.8125 |
|
Variance |
15.22619048 |
96.42410714 |
|
Observations |
7 |
8 |
|
Hypothesized Mean Difference |
0 |
|
|
Df |
9 |
|
|
t Stat |
-4.816944724 |
|
|
P(T<=t) one-tail |
0.000475506 |
|
|
t Critical one-tail |
1.833112923 |
|
|
P(T<=t) two-tail |
0.000951012 |
|
|
t Critical two-tail |
2.262157158 |
|
Notice that the two sample mean values (variance) are 25.64(15.23) and 43.81(96.42). The two tailed calculated t-statistic is 4.82 and the highlighted p-value for this test is p=0.001. (0.000951012) Since the p-value is less than 0.05, this provides evidence to reject the null hypothesis of equal means.
As an example of how this might be reported in a journal article:
Methods: A preliminary test for the equality of variances indicates that the variances of the two groups were significantly difference F=.157, p=.02. Therefore, a two-sample t-test was performed that does not assume equal variances.
Results: The mean score for group 1 (M=25.64 SD= 3.9021, N= 7) was significantly smaller than the scores for group 2 (M=42.81, SD=9.82, N= 8.) using the two-sample t-test for unequal variances, t(9) = -4.82, p <= 0.001. (Technically, the degrees of freedom for this unequal variances t-test should be 9.4 instead of 9, but Excel unfortunately rounds off the DF, so it is reported incorrectly. Years ago, it used to be conventional to round down if you were consulting a table for a probability level, but most statistical programs now calculate the correct p-value using a fractional DF though interpolation.)
Notice that the standard deviation is reported rather than the variances as shown in the Excel results table. You can calculate the standard deviation using Tools/ Data Analysis / Descriptive statistics.
When the variances are assumed equal, the analysis is similar, select Tools/ Data Analysis/ t-Test: Two Sample assuming Equal Variances
End of tutorial
See http://www.stattutorials.com/EXCEL
Other resources
- Check out WINKS SDA tutorials by clicking here.
-
For SPSS examples: 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. In this one-stop reference, authors TexaSoft and Wayne A. Woodward 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 |
© Copyright TexaSoft, 1996-2007