NORM.INV

What it does

NORM.INV(probability, mean, standard_dev)

NORM.INV(probability, mean, standard_dev) returns the quantile of the normal distribution with mean μ and standard deviation σ at probability p — the value x such that a normal random variable falls below x with probability exactly p:

NORM.INV(p, μ, σ) = x   where   F(x; μ, σ) = p

Because the normal CDF standardizes, the whole function reduces to the standard normal quantile — the probit function — composed with an affine map:

x = μ + σ · Φ⁻¹(p)
  = μ + σ √2 · erf⁻¹(2p − 1)

Domain and range. p must lie strictly inside (0, 1); p ≤ 0 and p ≥ 1 are documented #NUM! conditions, and σ ≤ 0 is another. On (0, 1) the function is a strictly increasing bijection onto the whole real line — so the range is unbounded, and it is unbounded in a very sharp way: as p → 0⁺ and p → 1⁻ the result runs to ∓∞. There are no poles in the interior and no branch cuts; Φ⁻¹ is analytic on (0, 1).

The endpoints are the entire difficulty. Differentiating the inverse relation,

d/dp Φ⁻¹(p) = 1 / φ(Φ⁻¹(p))

and φ vanishes faster than any power at ±∞, so the derivative blows up superpolynomially at both ends. Concretely, near p = 0,

Φ⁻¹(p)  ~  −√( 2 ln(1/p) − ln(2 ln(1/p)) − ln(2π) )      as p → 0⁺

so an absolute error δ in p produces an absolute error of roughly δ / φ(z) in the answer, which for z = −6 is a factor of about 10^8. The inverse normal is ill-conditioned at the tails by nature, not by implementation. No algorithm can do better than the conditioning allows, and the practical consequence is that NORM.INV at extreme p is only as good as the representation of p itself.

Special values and identities.

Φ⁻¹(1/2) = 0        exactly, so NORM.INV(0.5, μ, σ) = μ
Φ⁻¹(p) = −Φ⁻¹(1 − p)                the reflection identity
NORM.INV(Φ(x), μ, σ) = x            round-trip in x
Φ(NORM.INV(p, 0, 1)) = p            round-trip in p

NORM.INV(0.5, μ, σ) = μ is the one input whose answer is exactly nameable and it is the cheapest possible test of this surface.

Arguments

Microsoft's page gives three required arguments:

Microsoft further notes that with mean = 0 and standard_dev = 1, NORM.INV "uses the standard normal distribution".

The pinned catalogue supplies NORM.INV(probability, mean, standard_dev) with three required arguments.

Try it

Quirks

The documented algorithm

Microsoft's NORM.INV page states something that documentation of a mathematical function very rarely states: how it is computed.

"Given a value for probability, NORM.INV seeks that value x such that NORM.DIST(x, mean, standard_dev, TRUE) = probability."

"Thus, precision of NORM.INV depends on precision of NORM.DIST."

The companion NORM.S.INV page says the same thing about its own pair and adds the phrase "uses an iterative search technique".

Three consequences follow, and the Handbook states them as consequences of the documentation, not as observations of Excel:

1. NORM.INV is documented as a root-finder over NORM.DIST, not as a direct quantile approximation. That is architecturally different from every standard library implementation, which evaluates a rational approximation to Φ⁻¹ directly.
2. Its accuracy is documented as bounded above by NORM.DIST's. Whatever error the forward CDF carries is inherited, and then divided by φ(z) through the conditioning above — so the inherited error is amplified in the tails, not merely passed along.
3. Round-trip consistency is expected to be unusually good, and monotonicity is not guaranteed. A search that terminates on a tolerance will reproduce p well by construction, while adjacent p values can land on the same or non-monotone x if the termination rule is coarse. Those are opposite predictions from the ones a direct rational approximation makes, which is what makes them worth testing.

Whether Excel today actually performs an iterative search is not something this page asserts. The documentation says so; the Handbook has not observed it, and no evidence record in its possession identifies this surface's op-graph.

Result and edge cases

Returns Number.

• p = 0.5 returns μ exactly, for every admissible σ.
• p at the ends. p ≤ 0 and p ≥ 1 are documented #NUM!. The smallest positive double is about 4.9 × 10⁻³²⁴, whose quantile is about −38.4; the largest p strictly below 1 is 1 − 2⁻⁵³, whose quantile is about 8.13. The reachable range of NORM.INV(p, 0, 1) is therefore roughly [−38.4, +8.13] and is wildly asymmetric — because probabilities near 1 are represented with absolute spacing 2⁻⁵³ while probabilities near 0 are represented with relative precision all the way into the subnormals. This asymmetry is a fact about binary64, not about the normal distribution, and it is the reason serious tail work is done with Φ(−z) rather than 1 − Φ(z).
• σ ≤ 0 is a documented #NUM!, σ = 0 included.
• standard_dev very large or mean very large: the result is μ + σ z and can overflow; what Excel publishes at overflow is not established here.
• Arrays. The recorded profile is Custom; elementwise lifting is not settled.

Empty, missing and error arguments follow the shared call model; see Coercion and lifting.

Errors

As documented by Microsoft on the NORM.INV page (retrieved for this page):

Note that this page's #VALUE! row says "any argument", where the sibling NORM.DIST page names only mean and standard_dev. The two pages describe the same coercion behaviour with different completeness; NORM.INV's wording is the better one.

Error values in any argument propagate under the ordinary coercion rules.

How to implement

Numerical notes

1. The conditioning is the headline and it is not fixable. As derived above, the derivative of Φ⁻¹ is 1/φ(Φ⁻¹(p)), which grows without bound at both ends. Any error in p — including the rounding that put p into a double in the first place — is multiplied by that factor. At p = 10⁻¹⁰ the amplification is enormous. A function that is correctly rounded at every input is still delivering an answer whose meaning is limited by the input's representation. The right way to state accuracy for this function is in terms of the round trip, not the forward error.

2. Direct approximation is the standard route, and it is not what the documentation describes. The reference algorithm is Wichura's AS 241 (Applied Statistics 37, 1988), whose PPND16 gives about 16 significant digits over the whole representable range using three rational approximations selected by region: |p − ½| ≤ 0.425 on the central branch, then two tail branches parameterized in r = √(−ln min(p, 1−p)). This is what R's qnorm, Boost, and most statistical libraries use. Earlier and weaker: A&S 26.2.22 (about 3 × 10⁻³ absolute) and 26.2.23 (about 4.5 × 10⁻⁴), the Beasley–Springer algorithm AS 111 with Moro's tail modification, and Acklam's widely copied approximation — all fine for graphics and inadequate for a reference. Anyone implementing this function today should start from AS 241.

3. A root-finder over the CDF is a defensible design with distinctive failure modes. If the documented description is accurate, the properties to expect are:

• Excellent round-trip in p — the search terminates on that criterion, so NORM.DIST(NORM.INV(p, …), …, TRUE) ≈ p by construction;
• Possible non-monotonicity in x across adjacent p, if the termination tolerance is loose relative to the local dx/dp;
• Degrading accuracy in the tails at exactly the rate the conditioning predicts, because each evaluation of the forward CDF carries its own error and the search cannot beat it;
• Sensitivity to the bracketing strategy at extreme p, where the initial bracket must already reach −38 on one side.

Each of those is testable, and together they discriminate a search from a direct rational approximation. That is the highest-value experiment on this page.

4. Newton refinement is the bridge between the two designs. A standard high-accuracy implementation evaluates a rational approximation and then applies one or two Halley or Newton steps using the exact derivative φ(z):

z ← z − (Φ(z) − p) / φ(z)                    Newton
z ← z − u / (1 + z·u/2),  u = (Φ(z) − p)/φ(z)   Halley

Each step roughly squares (Newton) or cubes (Halley) the accuracy, and the derivative is available in closed form — which is why the inverse normal is one of the pleasanter quantiles to refine. An implementation that does this is using the forward CDF, which means its accuracy is also derivative of the CDF's, exactly as Microsoft's page says. The two descriptions are not as far apart as they first look.

5. Argument reduction. μ + σ z is a fused-multiply-add opportunity and a cancellation opportunity: when μ is large and σ z is small, the result loses the information in z entirely. This is the same ceiling described on NORM.DIST, and it is invisible to any test that uses μ = 0.

6. The published assessment record. McCullough and Wilson's series in Computational Statistics & Data Analysis, and Knüsel's assessments of Excel's distribution functions, are the standing published examinations of this lane, with revisions reported around Excel 2003 and again when the dotted names arrived. Morten Welinder's Gnumeric work is the standard practical account of implementing this family in a spreadsheet, including the inverses. Named as literature; no figure from them is restated here.

Rust code

3 norm_inv "NORM.INV" (3, 3) [V "probability", V "mean", V "standard_dev"] |s, a, sv| {
    let [p, m, sd_] = nums::<3>(s, a)?;
    out(s, nl::norm_inv_kernel(p, m, sd_))
}

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

• NORM.DIST is the forward function and, per Microsoft's own text, the thing NORM.INV searches over. The round trip in both directions is the natural test pair.
• NORM.S.INV is the standardized case, NORM.INV(p, 0, 1).
• CONFIDENCE.NORM is a direct consumer: a confidence half-width is NORM.S.INV of a tail probability, scaled.
• STANDARDIZE is the affine map this function composes with, run the other way.
• Confused with: NORM.DIST itself, by readers who want "the normal function" and pick the wrong direction; and NORM.INV(p, μ, σ) used with p expressed as a percentage, where 95 is a #NUM! and 0.95 is what was meant.

Named variants

No named variants are recorded yet.