Z.TEST

What it does

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

Z.TEST(array, x, [sigma]) returns the one-tailed probability value of a z-test: the probability of observing a sample mean at least as large as the one in array, if the underlying population mean were x.

Microsoft gives the definition as an explicit composition of other worksheet functions, which is unusual and useful:

Z.TEST(array, x, sigma) = 1 − NORM.S.DIST( (AVERAGE(array) − x) / (sigma/sqrt(n)), TRUE )

Z.TEST(array, x)        = 1 − NORM.S.DIST( (AVERAGE(array) − x) / (STDEV(array)/sqrt(n)), TRUE )

with n = COUNT(array). Writing the standardized statistic as

z = ( x̄ − x ) / ( σ / sqrt(n) )

the function returns 1 − Φ(z) = P(Z > z), the upper tail of the standard normal.

Range: (0, 1), strictly. The value is exactly ½ when x̄ = x, decreases toward 0 as the sample mean rises above the hypothesized mean, and rises toward 1 as it falls below. Microsoft states the direction explicitly: "if AVERAGE(array) < x, Z.TEST will return a value greater than 0.5."

Two structural facts distinguish this from every other test surface in Excel:

1. It is directional. Unlike T.TEST, which takes the absolute value of its statistic and is blind to which mean is larger, Z.TEST returns an upper-tail probability that depends on the sign. Swapping the roles of the sample and the hypothesized mean changes the answer to its complement, not to itself. A two-tailed value therefore has to be built by the caller, and Microsoft supplies the recipe: =2 * MIN(Z.TEST(array, x, sigma), 1 − Z.TEST(array, x, sigma)).
2. The default is not the test the name implies. When sigma is omitted, the documented formula substitutes the sample standard deviation while keeping the normal distribution for the tail. That combination is not a z-test and not a t-test; it is a z-test using an estimated σ, which is only asymptotically correct. For small n it understates the tail. Microsoft documents the substitution plainly; the statistical consequence is not commented on there, and a reader who wants the exact small-sample answer wants a one-sample t-test, which Excel does not offer directly.

Arguments

Note a small documentation staleness: the defining formula names STDEV, the legacy spelling, inside the page for a modern dotted-name function. STDEV and STDEV.S are documented equivalents, but the modern page's formula reaches for the compatibility name.

Note a larger one: the remark defining what the function represents introduces a symbol μ0 ("when the underlying population mean is μ0") that appears nowhere else on the page and is never tied to the argument x. Read strictly, the sentence defines the function in terms of an undefined quantity. The intended reading is obvious and this page states it — μ0 is x — but the Handbook records the defect rather than silently repairing it.

Try it

Quirks

Result and edge cases

Returns Number, a probability in (0, 1).

• x̄ = x gives exactly ½. Free and exact, and the cheapest diagnostic on the surface.
• Large positive z is where the documented formula fails, and it fails hard. See Numerical notes: 1 − Φ(z) computed by subtraction saturates at zero once Φ(z) rounds to 1, which happens beyond roughly eight standard errors. Every p-value smaller than that is published as zero, whatever the data says. This is a property of the documented composition, not a defect discovered by measurement.
• Large negative z is the well-conditioned direction: the answer approaches 1 from below and Φ(z) carries it accurately.
• sigma = 0 makes the statistic infinite. Undocumented.
• Zero-variance array with sigma omitted is the same situation with more data.
• Text, logicals and empty cells in array follow the family's coercion policy; the engine's battery rejects a logical in the array position. Undocumented on Microsoft's page, which says nothing about the contents of array beyond that it must not be empty.
• COUNT semantics. The documented n is COUNT(array), which counts numbers only. So a range mixing numbers and text has an n that differs from its cell count, and the documented formula's AVERAGE and STDEV use the same convention. Consistent, but worth stating.

Errors

As documented on Microsoft's Z.TEST page:

That is the entire documented error table — one row, for a function with a divisor that can be zero, an optional argument that can be negative, and a data argument whose admissible contents are never described. Undocumented and therefore open: sigma ≤ 0, n = 1, zero-variance data with sigma omitted, and non-numeric contents of array.

Errors in any argument propagate under the universal coercion rule. The Handbook has not verified any of this against Excel.

How to implement

Numerical notes

The documented formula has a hard accuracy floor, and it is visible on the documentation page itself. 1 − Φ(z) is a subtraction from one. Once Φ(z) rounds to 1.0 in double precision — which happens beyond roughly eight standard errors — the difference is exactly zero, and every smaller p-value collapses to zero with it. A z-test on a well-powered study routinely produces statistics past that point; the whole scientific content of such a result is the size of the tiny p-value, and this composition discards it.

The fix is standard and old: compute the upper tail directly rather than as a complement. The identity is exact,

1 − Φ(z) = ½ · erfc( z / sqrt 2 )

and erfc exists precisely so that the small quantity is computed as itself. Cody's rational approximations for erf/erfc, the fdlibm implementations, and Boost.Math's erfc are the standard sources, and the far-tail asymptotic erfc(t) ~ exp(−t²)/(t·sqrt π) · (1 − 1/(2t²) + …) is what carries the value once the approximation regions run out. A normal-tail routine that switches to the asymptotic keeps returning meaningful values until exp(−z²/2) itself underflows, which is dozens of standard errors out rather than eight.

Excel's surface set does not offer a complementary normal CDF, which is why the composition is written the way it is. ERFC is the workaround, and it is the honest recommendation to a reader who needs small p-values.

Secondary hazards, all real but smaller:

1. The sample standard deviation. With sigma omitted, the default path computes a variance, and the usual accumulation argument applies — never Σx² − (Σx)²/n; see VAR.S and Higham's §1.9. A data set with a large mean relative to its spread can lose most of the digits of s before the statistic is formed.
2. The mean difference. x̄ − x is a subtraction of near-equal quantities exactly when the null hypothesis is nearly true, which is the interesting case.
3. Composition ordering. (x̄ − x)/(σ/sqrt n) and (x̄ − x)·sqrt(n)/σ are the same real number and different floating-point numbers; the second avoids one division and one rounding. Which one Excel uses is unknown, and the documented formula shows only the first.

The accuracy of Excel's normal-distribution and hypothesis-test surfaces is a long-standing topic in the published literature — Morten Welinder's work on Gnumeric's statistical functions is the most concrete account of the failure modes, and the assessments by Knüsel and by McCullough & Wilson are the standing evaluations. The Handbook names them as the right reading and does not assert from them what any current build does.

Rust code

106 z_test "Z.TEST" (2, 3) [R "array", V "x", V "sigma"] |s, a, sv| { z_test_of(s, a, sv) }

No implementation has been published with a Handbook test suite for this function.

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• ZTEST is the legacy compatibility spelling. Neither spelling appears in the Handbook's legacy/modern alias-pairing record, so nothing in the Handbook's record bears on whether they publish the same bits.
• NORM.S.DIST is the function the documented formula calls, and therefore the surface whose accuracy Z.TEST inherits. This surface's module carries the open defect stream BUG-FUNC-013, whose title names a normal-distribution exact-value accuracy gap, and BUG-FUNC-021, whose title names a statistical numeric exactness drift. Both are cited by name only.
• ERFC is the escape hatch. 1 − Φ(z) = ½·ERFC(z/sqrt 2) exactly, and ERFC is a complementary surface computed directly rather than by subtraction — so =0.5*ERFC((AVERAGE(a)-x)/(s/SQRT(n))/SQRT(2)) reproduces Z.TEST in the well-conditioned region and keeps working where Z.TEST's documented composition has saturated to zero. Whether Excel's ERFC is itself accurate in the far tail is a separate question, on a separate page.
• T.TEST is the small-sample counterpart — and note the shape mismatch: it takes two samples, not a sample and a hypothesized mean, and it is direction-blind. There is no one-sample T.TEST. A one-sample t-test must be assembled from AVERAGE, STDEV.S, COUNT and T.DIST.RT.
• STANDARDIZE computes the z this function's formula forms internally.
• Confused with: the Data Analysis ToolPak's "z-Test: Two Sample for Means", which is a different (two-sample) test on a different code path.

Named variants

No named variants are recorded yet.