Using Excel to Perform a Student’s t-test
The Student’s t-test is used for comparing the mean score in one condition to the mean score in another condition to see if there is a significant and meaningful difference between groups. Results are reported as the p-value, which is a probability of the differences in the groups occurring by chance. The lower the p–value, the less likely the differences between groups are coincidental and the more likely they are legitimate. Typically, if p<0.05 then differences between groups are considered significant.
Although it is possible to calculate a t-test by hand, it is much more convenient to use Excel to calculate the t-test for you. In order to calculate the t-test, the following information must be entered into the Excel spreadsheet depicted above:
· Group 1 & Group 2 data
o Enter individual values for Group 1 and Group 2 in the yellow cells in column A and column B of the Excel spreadsheet.
· Tail number (1 or 2)
o A two-tailed t-test is selected when the hypothesis being tested does not indicate a direction of change. For example, the mean blood pressure of people taking Drug B will be different then the mean blood pressure of people in the control group. To select a two-tail test, enter a “2” in cell B2 of the Excel spreadsheet.
o A one-tailed test is used when the hypothesis being tested indicates a direction of change. For example, the mean weight of mice given Supplement Z will be greater than the mean weight of mice in the control group. To select a one-tail test, enter a “1” in cell B2 of the Excel spreadsheet.
· Test type (paired, equal variance, or unequal variance)
o Paired sample t-tests evaluate a group of matched pairs, or one group that has been tested twice. For example, the blood sugar of a group of type I diabetics is taken before and after a new medication. To select a paired sample test, enter a “1” in cell B3 of the Excel spreadsheet.
o Equal and unequal variance tests are used when comparing values from two separate groups. For example, the height of a group of pea plants given fertilizer is compared to the height of a group of untreated pea plants. The difference between the unequal and equal variances test types will be reserved for your introductory statistics course. For the purposes of this class, if two separate groups are being compared select the equal variances test type, which is done by entering a “2” in cell B3 of the Excel spreadsheet.
Once the required information is entered into the Excel spreadsheet, it will populate with the mean and standard deviation for each group, as well as the p-value for the t-test. In the example spreadsheet below, the p-value is reported in cell F8 as 0.019. Therefore, the mean of Group 1 and mean of Group 2 are significantly different from each other.