Excel functions / Statistics
T.TEST function
Student's t-test: the chance that two samples would differ this much if they came from populations with the same mean.
Syntax
=T.TEST(array1, array2, tails, type)
Arguments
array1: The first sample.array2: The second sample.tails: 1 for a one-tailed test, 2 for a two-tailed test.type: 1 paired, 2 two samples with equal variance, 3 two samples with unequal variance.
When to use it
T.TEST compares two sets of numbers and gives a p-value: the chance of seeing a difference this large between their averages if there were really no difference at all. A small p-value, by convention below 0.05, is taken to mean the difference is real.
The last two arguments matter. Tails is 2 unless you only care about a difference in one direction. Type is 1 when the two sets are the same things measured twice (paired), 2 for two separate groups with similar spread, and 3 for two separate groups whose spread may differ.
Examples
The same six people, before and after training
A B C D 1 Before After 2 72 78 0.0010 3 68 71 4 75 80 5 80 82 6 66 70 7 74 79 In D2:
=T.TEST(A2:A7, B2:B7, 2, 1)→ 0.0010Paired, because each row is one person. A p-value this small says the improvement is very unlikely to be chance.
The same numbers treated as two separate groups
A B C D 1 Before After 2 72 78 0.1802 3 68 71 4 75 80 5 80 82 6 66 70 7 74 79 In D2:
=T.TEST(A2:A7, B2:B7, 2, 3)→ 0.1802Ignoring the pairing throws away information, and the difference no longer looks convincing.
Common mistakes
- The wrong type. Before-and-after measurements on the same subjects are paired (type 1). Using type 2 or 3 for them makes a real effect much harder to see.
- Reading the p-value as the size of the effect. A small p-value says a difference is unlikely to be chance. It says nothing about whether the difference is large enough to matter.
- Paired ranges of different lengths. A paired test needs the same number of values in both ranges, or the result is #N/A.
Common questions
How do I do a t-test in Excel?
Put the two sets of numbers in two columns and use =T.TEST(first, second, 2, type), where type is 1 for paired data, 2 for two groups with equal variance and 3 for unequal variance. The result is the p-value.
T.TEST in other languages
Excel shows function names in its own language. In these languages it also puts a semicolon between arguments where English has a comma.
- Czech: T.TEST
- Danish: T.TEST
- Dutch: T.TEST
- Finnish: T.TESTI
- French: T.TEST
- German: T.TEST
- Hungarian: T.PRÓB
- Italian: TESTT
- Norwegian: T.TEST
- Polish: T.TEST
- Portuguese (Brazil): TESTE.T
- Portuguese (Portugal): TESTE.T
- Russian: СТЬЮДЕНТ.ТЕСТ
- Spanish: PRUEBA.T.N
- Swedish: T.TEST
- Turkish: T.TEST
Also written as TTEST.
Related functions
- T.DIST.2T: The two-tailed t-distribution: the chance of a t further from 0 than x, on either side. This is the p-value of a two-sided t-test.
- T.INV.2T: The t-value with a given share of the distribution beyond it, counting both tails. This is the critical value of a two-sided test.
- F.TEST: The F-test: the chance that two samples would have variances this different if their populations varied equally. Two-tailed.
- CHISQ.TEST: The chi-square test of independence: the chance of counts this far from the expected ones if nothing but chance were at work.
- Z.TEST: The one-tailed z-test: the chance of a sample mean this far above a claimed population mean.
- STDEV: Standard deviation of a sample.