Z.TEST Function

Returns the one-tailed p-value of a z-test against a hypothesised mean.

Category

Statistical

Available in

Excel 2010+, Microsoft 365

Returns

A number

Want to practice Z.TEST in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

Z.TEST checks whether a sample's average is higher than a value you expected (x). It returns the one-sided p-value: the chance of a sample mean this high if the true mean were x.

Give sigma if you know the population standard deviation; otherwise Excel uses the sample's own standard deviation. For the opposite direction use 1 − Z.TEST; for two-sided, 2 × MIN(Z.TEST(…), 1 − Z.TEST(…)).

Syntax

=Z.TEST(array, x, [sigma])

Arguments

ArgumentRequired?What it does
arrayYesThe sample.
xYesThe mean you're testing against.
sigmaNoThe known population standard deviation; if left out, the sample standard deviation is used.

Examples

Example 1 — Is the new average above $44?

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

E2fx=Z.TEST(C2:C9,44)
ABCDE
1StoreBeforeAfterMeasureResult
2Store 14246p (sample sd)0.0726
3Store 23844p (sigma = 3)0.0974
4Store 34549Avg after45.38
5Store 44043
6Store 53741
7Store 64448
8Store 74147
9Store 83945
=Z.TEST(C2:C9,44) Explain

p ≈ 0.07: a $45.38 average from only eight stores isn't strong evidence that the true average is above $44.

Example 2 — Two-sided version

Test for any difference from $44, up or down.

=2*MIN(Z.TEST(C2:C9,44),1-Z.TEST(C2:C9,44)) Explain

Common mistakes

  • One-sided only. Z.TEST tests for a mean above x. Use the two-sided formula above when either direction matters.
  • #N/A array is empty.
  • Small samples. With few values and no known sigma, a t-test (T.TEST or T.DIST) is more appropriate.

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.