CONFIDENCE.NORM

What it does

CONFIDENCE.NORM(alpha, standard_dev, size)

CONFIDENCE.NORM(alpha, standard_dev, size) returns the half-width of a two-sided confidence interval for a population mean, under the assumption that the population standard deviation is known and the sampling distribution of the mean is normal.

Writing Φ for the standard normal cumulative distribution function and z_q = Φ⁻¹(q) for its quantile,

CONFIDENCE.NORM(α, σ, n)  =  z_{1 − α/2} · σ / √n

and the interval Microsoft's page states is

x̄ ± CONFIDENCE.NORM(α, σ, n)

where x̄ is the sample mean. The confidence level is 100·(1 − α)%, so α = 0.05 gives the familiar 95% interval and z_{0.975} ≈ 1.959963985….

Three things follow from the formula and are worth stating plainly:

1. The function never sees your data. It takes a spread and a count, not a sample. It is a scaling of one normal quantile — the entire mathematical content is Φ⁻¹.
2. It returns a half-width, not an interval. Readers who expect a pair of endpoints get a single number; the endpoints have to be assembled by hand.
3. σ is treated as known. That is the modelling assumption that separates this function from CONFIDENCE.T, which uses a Student's t quantile with n − 1 degrees of freedom because the spread was estimated from the sample.

Domain and range. The mathematical function is defined for α ∈ (0, 1), σ > 0, n ≥ 1, and is positive on all of it. It is decreasing in α (a higher confidence level widens the interval), linear and increasing in σ, and decreasing in n like n^(−1/2) — the familiar square-root law that makes halving an interval cost four times the sample. As α → 0⁺ the value diverges, because Φ⁻¹(q) → +∞ as q → 1⁻; as α → 1⁻ it tends to 0.

Arguments

The commonly misunderstood position is the first. alpha is the significance level, not the confidence level: a reader who wants 95% must pass 0.05, and passing 0.95 silently produces the 90%-two-tailed half-width instead of an error. Nothing in the value or the type catches that mistake.

All three are numeric slots subject to ordinary to-number coercion; see Coercion and lifting.

Try it

Quirks

Result and edge cases

Returns Number — a positive half-width.

Documented boundary behaviour, from Microsoft's page:

• Non-numeric argument → #VALUE!.
• alpha ≤ 0 or alpha ≥ 1 → #NUM!. Both ends are closed against you: alpha = 0 would be an infinite interval and alpha = 1 a degenerate one, and neither is admitted.
• standard_dev ≤ 0 → #NUM!. A zero spread is rejected rather than returning a zero width.
• size non-integer → truncated; size < 1 → #NUM!.

Errors

As documented on Microsoft's CONFIDENCE.NORM page:

How to implement

Numerical notes

The whole numerical difficulty is Φ⁻¹, and it is a genuinely hard function to evaluate well.

• Why it is hard. Φ⁻¹(q) has infinite slope at both ends of (0,1) and the naive route — invert erf by iteration on a series for Φ — loses relative accuracy in the tails, where the quantity of interest is large and the probability that determines it is tiny. Every digit of q near 1 is worth a great deal of z, so cancellation in forming 1 − α/2 is a real hazard.
• The literature. Abramowitz & Stegun chapter 26 gives the classical rational approximations (26.2.22, 26.2.23) with their absolute-error bounds — good enough for tables, not for double precision. The modern standard is Wichura's AS 241 (PPND16), accurate to about 16 significant digits over the whole open interval; Moro's and Acklam's approximations are the usual fast alternatives, and Cephes and Boost both carry well-tested ndtri/erfc_inv routines. Anyone implementing this function is really choosing among those.
• The rest is trivial and still worth care. σ/√n is one square root and one division. Forming z · σ / √n as (z * σ) / sqrt(n) and as z * (σ / sqrt(n)) differ in the last bit; a compatibility implementation has to pick the same association Excel picks, and the Handbook does not know which that is.
• Truncation before the square root. Because size is truncated first, the divisor is √⌊n⌋, not √n. An implementation that truncates after taking the root would agree on integer inputs and disagree everywhere else — a bug that hides from every integer test case.

Do not read any of this as a claim about Excel's internal algorithm. What the Handbook can say is that this surface's accuracy is exactly the accuracy of whatever normal quantile sits underneath it, and that nobody has measured that here.

Rust code

14 confidence_norm "CONFIDENCE.NORM" (3, 3) [V "alpha", V "standard_dev", V "size"] |s, a, sv| {
    let [alpha, sd_, n] = nums::<3>(s, a)?;
    out(s, nl::confidence_norm_kernel(alpha, sd_, n))
}

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

• CONFIDENCE.T — the sibling for an estimated standard deviation. The two differ by exactly one substitution: z_{1−α/2} becomes t_{1−α/2, n−1}. Because t_{q,ν} > z_q for every finite ν, CONFIDENCE.T is always the wider of the two on the same inputs, converging to CONFIDENCE.NORM as n → ∞. Choosing the normal form when the spread came from the sample understates the interval, and for small n it understates it badly.
• NORM.S.INV — the quantile this function calls. CONFIDENCE.NORM(α, σ, n) and NORM.S.INV(1 − α/2) * σ / SQRT(n) are the same expression; the second is the one to use when you want to see the pieces.
• Z.TEST — the hypothesis test that pairs with this interval.
• Confused with: CONFIDENCE.NORM is not a margin of a proportion. Applying it to a proportion requires σ = √(p(1−p)), which the function will not compute for you.

Named variants

No named variants are recorded yet.