Table of Contents
To perform a t-Test with unequal variances in Excel, first enter the data in two columns in an Excel spreadsheet. Then select the ‘Data Analysis’ option from the Data tab. Then choose the ‘t-Test: Two-Sample Assuming Unequal Variances’ option from the list. In the dialog box, enter the input range for each sample, select the output range and click ‘OK’. This will run the t-Test and display the results in the output range.
A two sample t-test is used to determine whether or not two are equal.
There are two versions of the two sample t-test you can use:
- t-test with equal variances
- t-test with unequal variances
We use a t-test with unequal variances when the variances in the two samples are not equal.
The easiest way to determine if the variances between the two samples are equal is to use the variance rule of thumb.
As a rule of thumb, if the ratio of the larger variance to the smaller variance is less than 4 then we can assume the variances are approximately equal.
Otherwise, if the ratio is equal to or greater than 4, we assume that the variances are not equal.
The following step-by-step example shows how to perform a two sample t-test with unequal variances in Excel.
Step 1: Enter the Data
Suppose we would like to determine if two different studying methods lead to different mean exam scores among students at some university.
We select a random sample of 20 students to use each studying method and record their exam scores:
Step 2: Determine Equal or Unequal Variance
Next, we can calculate the ratio of the sample variances:
Here are the formulas we typed into each cell:
- Cell E1: =VAR.S(A2:A21)
- Cell E2: =VAR.S(B2:B21)
- Cell E3: =E1/E2
Since this value is greater than or equal to 4, we assume that the variances between the two samples are not equal.
Step 3: Perform Two-Sample t-Test with Unequal Variance
Next, we can perform a two sample t-test with unequal variances to determine if the mean exam score between the two samples is equal.
To do so, click the Data tab along the top ribbon, then click the Data Analysis button in the Analyze group:
If you don’t see this button, you need to first .
In the new window that appears, click t-test: Two-Sample Assuming Unequal Variances, then click OK:
In the new window that appears, fill in the following information, then click OK:
Once you click OK, the results of the two sample t-test will appear:
Step 4: Interpret the Results
From the output we can see:
- The mean exam score for method 1 was 80.15.
- The mean exam score for method 2 was 87.8.
- The t test-statistic was -3.09623.
- The corresponding two-tailed p-value was 0.004532.
Since this p-value is less than .05, we can conclude that there is a statistically significant difference in the mean exam scores between the two studying methods.