T.TEST Function

Returns the p-value of a t-test comparing the means of two samples.

Category

Statistical

Available in

Excel 2010+, Microsoft 365

Returns

A number

Taught hands-on in Data Analytics · Testing Ideas module

Overview

T.TEST checks whether two sets of numbers have genuinely different averages, or whether the gap could just be chance. It returns a p-value. A p-value is the chance of seeing a difference at least this big if there were really no difference. Below 0.05 is the usual cut-off for calling a result statistically significant.

type chooses the test: 1 paired (the same items measured twice, such as stores before and after a change), 2 two independent samples with equal variance, 3 two independent samples with unequal variance (the safer choice when unsure).

Syntax

=T.TEST(array1, array2, tails, type) Explain

Arguments

ArgumentRequired?What it does
array1YesThe first sample.
array2YesThe second sample (the same size for a paired test).
tailsYes1 for a one-sided test, 2 for two-sided.
typeYes1 paired, 2 two-sample equal variance, 3 two-sample unequal variance.

Examples

Example 1 — Did the redesign raise order values?

Average order value ($) at eight Northstar stores, before and after a checkout redesign.

E2fx=T.TEST(B2:B9,C2:C9,2,1)
ABCDE
1StoreBeforeAfterMeasureResult
2Store 14246Paired p0.00001
3Store 23844Unpaired p0.0046
4Store 34549Avg before40.75
5Store 44043Avg after45.38
6Store 53741
7Store 64448
8Store 74147
9Store 83945
=T.TEST(B2:B9,C2:C9,2,1) Explain

Every store rose by $3–6, so the paired test is overwhelming (p ≈ 0.00001). Ignoring the pairing still gives p ≈ 0.005 — both far below 0.05.

Example 2 — Two independent groups, equal variance

If the two columns were different stores, use type 2 (or 3).

=T.TEST(B2:B9,C2:C9,2,2) Explain

Returns about 0.0046.

Common mistakes

  • It returns a p-value, not t. T.TEST gives the probability directly. For the t statistic itself, use the Analysis ToolPak or calculate it.
  • #N/A A paired test (type 1) with ranges of different sizes.
  • #NUM! tails isn't 1 or 2, or type isn't 1, 2 or 3.
  • Significant isn't the same as important. With big samples, tiny differences become significant. Check the size of the gap as well.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.