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
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
Arguments
| Argument | Required? | What it does |
|---|---|---|
| array | Yes | The sample. |
| x | Yes | The mean you're testing against. |
| sigma | No | The 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.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Store | Before | After | Measure | Result |
| 2 | Store 1 | 42 | 46 | p (sample sd) | 0.0726 |
| 3 | Store 2 | 38 | 44 | p (sigma = 3) | 0.0974 |
| 4 | Store 3 | 45 | 49 | Avg after | 45.38 |
| 5 | Store 4 | 40 | 43 | ||
| 6 | Store 5 | 37 | 41 | ||
| 7 | Store 6 | 44 | 48 | ||
| 8 | Store 7 | 41 | 47 | ||
| 9 | Store 8 | 39 | 45 |
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.
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
- Z.TEST replaces the older ZTEST function.
- Related functions: T.TEST, NORM.S.DIST, CONFIDENCE.NORM.

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.