CONFIDENCE.T Function

Returns the margin of error for a mean, using the t distribution.

Category

Statistical

Available in

Excel 2010+, Microsoft 365

Returns

A number

Taught hands-on in Data Analytics · Testing Ideas module

Overview

CONFIDENCE.T returns the ± margin around a sample average when the standard deviation comes from the sample itself — the usual situation. It uses the t distribution, so the margin is wider than CONFIDENCE.NORM's for small samples.

alpha is 1 minus the confidence level: 0.05 for 95%.

Syntax

=CONFIDENCE.T(alpha, standard_dev, size) Explain

Arguments

ArgumentRequired?What it does
alphaYes1 − confidence level, such as 0.05 for 95%.
standard_devYesThe sample standard deviation; must be greater than 0.
sizeYesThe sample size; at least 2.

Examples

Example 1 — Confidence interval for the new average

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

E2fx=CONFIDENCE.T(0.05,STDEV.S(C2:C9),COUNT(C2:C9))
ABCDE
1StoreBeforeAfterMeasureResult
2Store 14246Margin (95%)2.23
3Store 23844Avg after45.38
4Store 34549Low43.14
5Store 44043High47.61
6Store 53741
7Store 64448
8Store 74147
9Store 83945
=CONFIDENCE.T(0.05,STDEV.S(C2:C9),COUNT(C2:C9)) Explain

The true average order after the redesign is likely between about $43.14 and $47.61.

Example 2 — t vs. normal for a small sample

Standard deviation 1.5 with only 8 values:

=CONFIDENCE.T(0.05,1.5,8) Explain

Returns about 1.25, compared with 1.04 from CONFIDENCE.NORM(0.05,1.5,8).

Common mistakes

  • #DIV/0! size is 1.
  • #NUM! alpha is 0 or less or 1 or more, or standard_dev is 0 or negative.
  • alpha, not confidence. Pass 0.05 for 95%.

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.