Z.TEST in Excel: Upper-Tail Probability and ZTEST Migration
Use Z.TEST with known or estimated sigma, distinguish upper-tail and two-sided probabilities, and migrate legacy ZTEST formulas without assuming identical numerical implementations.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Z.TEST calculates a normal upper-tail probability for a hypothesized population mean. It returns a probability, not the standardized z statistic and not the probability that the hypothesis is true. The direction matters: an observed mean below the hypothesized mean gives a result above 0.5.
Syntax and the optional standard deviation
=Z.TEST(array, x, [sigma])
array supplies the observations; x is the hypothesized mean. sigma, when supplied, is the known population standard deviation. When omitted, Excel substitutes the sample standard deviation but still evaluates the normal tail. The empty-array error documented on this page is #N/A. Microsoft Z.TEST reference
Write μ0 for the hypothesized mean and xbar for the observed mean to avoid confusing them. With n observations, the model is:
z = (xbar − μ0) / (sigma / sqrt(n))
p = 1 − Φ(z)
Φ is the standard normal cumulative distribution function. Omitted sigma replaces it with the sample standard deviation s. That substitution does not turn the function into a Student t procedure. A sampling interpretation requires an appropriate model: independent observations and a justified normal sampling approximation, or normally distributed measurements with known spread. These fictional four-point examples illustrate arithmetic, not an automatic recommendation to use normal inference for small samples.
Worked known-sigma example
Enter A2:A5 as 8, 10, 12 and 14. Their mean is 11 and count is 4. Suppose the null mean is 10 and the known population standard deviation is 2:
=Z.TEST(A2:A5,10,2)
The standard error is 2/sqrt(4) = 1. Therefore z = (11−10)/1 = 1, and the independently calculated upper-tail probability is 0.158655253931457. The equivalent distribution calculation is 1-NORM.S.DIST(1,TRUE); the figure shown is mathematical reference precision, not a guarantee of matching application digits.
| Formula with this same dataset | Independent expectation |
|---|---|
=Z.TEST(A2:A5,10,2) |
0.158655253931457 |
=Z.TEST(A2:A5,12,2) |
0.841344746068543 |
=Z.TEST(A2:A5,11,2) |
0.5 |
=Z.TEST(A2:A5,10,4) |
0.308537538725987 |
Raising the hypothetical mean to 12 produces z=−1, reversing the side of the normal curve. Doubling sigma to 4 halves z to 0.5. Sigma has the same measurement units as the data; it is not a variance or a percentage unless those are actually the measurement units.
Two-sided and lower-tail calculations
For a two-sided alternative, distance on either side of the null matters. With the first formula’s result p, the symmetric two-sided probability is 2×min(p,1−p). An explicit worksheet formula is:
=2*MIN(Z.TEST(A2:A5,10,2),1-Z.TEST(A2:A5,10,2))
The independent result is 0.317310507862914. Changing the null mean from 10 to 12 leaves this two-sided value unchanged, although the upper-tail result changes. For a prespecified lower-tail alternative, use 1−p. Choose the alternative based on the question before interpreting the observations; selecting whichever tail is smaller after looking at the data changes the procedure.
A p-value is conditional on the assumed model. It is not effect size, practical importance, a confidence interval, or the probability that another sample will replicate the finding.
What happens when sigma is omitted?
The deviations around 11 are −3, −1, 1, 3, giving sample variance 20/3. Thus s=sqrt(20/3), standard error=sqrt(5/3), and z=sqrt(3/5)≈0.7745966692 for null mean 10.
=Z.TEST(A2:A5,10)
Its independent normal-tail expectation is approximately 0.2192890130405. Do not silently label that result a t-test p-value. A model requiring estimated variance and a t reference distribution needs a suitable t procedure; changing only a label cannot supply it.
Data checks, compatibility and limits
Use a numeric observation column, verify its count and units, and supply a positive known sigma where that is the intended model. The dedicated reference does not provide a comprehensive invalid-sigma or mixed-input error table, so specific error tokens for those boundaries are not asserted here. A zero denominator cannot support the displayed standardization.
Older workbooks can contain ZTEST(array,x,[sigma]). Microsoft retains that spelling for backward compatibility and points readers to Z.TEST for new work. For the same simple numeric example, the documented model gives the same mathematical reference value; this is not a claim that every build’s implementations are numerically identical. Compatibility-supported does not mean removed. Microsoft’s ZTEST migration notice
Related: NORM.S.DIST supplies the normal CDF; AVERAGE and COUNT help inspect the sample; T.TEST has a different test contract.
Availability and evidence
Microsoft’s dedicated reference, checked October 18, 2026, lists Excel for Microsoft 365 on Windows and Mac, Excel 2024 on Windows and Mac, Excel 2021 on Windows and Mac, Excel 2019 and Excel 2016. This is the documented product scope, not a record of execution on those releases. First introduction and last functioning build are unknown; the product list does not establish either. The reference continues to document this function; it does not establish a retirement date.
The examples here are independently calculated or explicitly identified as documented expectations. No Excel application or other spreadsheet engine was run, and no native screenshots are supplied. Very large, ill-conditioned or locale-sensitive inputs require separate validation.