The T.TEST formula
=T.TEST(array1, array2, tails, type)
| Argument | What to enter |
|---|---|
| array1 | The first group of numbers, for example B2:B8 |
| array2 | The second group, for example C2:C8 |
| tails | 2 if you're testing for any difference (the usual choice), 1 if you predicted the direction in advance |
| type | 1 paired: the same people or items measured twice 2 two independent groups with similar spread (equal variance) 3 two independent groups with different spread (unequal variance, Welch's t-test) |
The result is a p-value, not the t statistic. The older name TTEST works the same way.
Example 1: a paired t-test (before and after)
Seven students took a test before and after a training course. Same students, measured twice, so the data is paired.
Did the training change the scores?
In C10, use T.TEST to get the two-tailed p-value for the before and after scores.
How to read the p-value
- p below 0.05: the difference is statistically significant at the usual 5% level. It's unlikely to be just chance.
- p of 0.05 or above: you can't rule out that the difference is chance. That isn't proof that there's no difference.
- Pick the significance level (usually 0.05) before you look at the result.
Example 2: two independent groups (Welch's t-test)
Two different stores, different days, different numbers of days: independent groups. When you're not sure the spread is equal, type 3 is the safe choice.
Do the two stores sell differently?
In C12, use T.TEST to compare daily sales of Store A (B2:B10) and Store B (C2:C9), two-tailed, unequal variance.
Which type should I use?
| Your data | Type |
|---|---|
| Same people, items or places measured twice (before/after, left/right) | 1, paired |
| Two separate groups, similar spread | 2, equal variance |
| Two separate groups, spread unknown or different | 3, unequal variance (Welch) |
If you also need the t statistic and degrees of freedom, Excel's Analysis ToolPak (Data › Data Analysis) has t-test tools that print a full table. For the standard deviation of each group, use =STDEV.S(range).
Using Excel for more than statistics?
Take the free 10-minute skills test and see which Excel skills to brush up.
Frequently asked questions
What does T.TEST return in Excel?
The p-value: the probability of seeing a difference at least this large if there were no real difference between the groups. It does not return the t statistic.
Should I use a one-tailed or two-tailed t-test?
Use two tails unless you decided before collecting the data that only one direction matters. A one-tailed p-value is half the two-tailed one, so choosing it afterwards overstates the result.
What is the difference between type 2 and type 3?
Type 2 assumes both groups have the same variance; type 3 (Welch's t-test) doesn't. When in doubt, type 3 is the safer choice.
Why does T.TEST give #N/A?
For a paired test (type 1), both ranges must have the same number of values. Different lengths give #N/A.