NORM.S.INV

What it does

NORM.S.INV(probability)

NORM.S.INV(probability) returns the quantile of the standard normal distribution — mean 0, standard deviation 1 — at probability p. It is the probit function:

NORM.S.INV(p) = Φ⁻¹(p) = z    where    Φ(z) = p
              = √2 · erf⁻¹(2p − 1)

Domain and range. p must lie strictly inside (0, 1); p ≤ 0 and p ≥ 1 are documented #NUM!. On that open interval Φ⁻¹ is a strictly increasing, analytic bijection onto the whole real line. No poles in the interior, no branch cuts; the singularities sit at the two endpoints, and they are essential in the practical sense — the function is unbounded there.

The endpoints are the whole story. From the inverse-function rule,

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

and φ decays like e^(−z²/2), so the derivative grows superpolynomially at both ends. The asymptotic form of the function itself is

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

— a square root of a logarithm, which is why the function is so flat in the middle and so steep at the edges, and why every good implementation reparameterizes the tails in r = √(−ln min(p, 1−p)) rather than in p.

Identities and special values.

Φ⁻¹(1/2) = 0                      exactly
Φ⁻¹(p) = −Φ⁻¹(1 − p)              reflection; exact
Φ(Φ⁻¹(p)) = p,   Φ⁻¹(Φ(z)) = z    round trips
Φ⁻¹(0.975) ≈ 1.959963984540054    the two-sided 95% constant

Φ⁻¹(1/2) = 0 is the only exactly nameable value, and it is this function's cheapest test. The 0.975 quantile is worth listing because it is the number every confidence interval in every textbook is built from, and because "1.96" is the rounding of it that people memorize.

The reachable range is asymmetric, and that is a binary64 fact. The smallest positive double is about 4.9 × 10⁻³²⁴, whose probit is about −38.5; the largest double strictly below 1 is 1 − 2⁻⁵³, whose probit is about +8.13. So NORM.S.INV can reach far into the left tail and barely into the right one — not because the distribution is asymmetric (it is not) but because probabilities near 1 are stored with absolute spacing 2⁻⁵³ while probabilities near 0 keep relative precision into the subnormals. Anyone wanting a large positive quantile must compute −NORM.S.INV(q) with q the small upper-tail probability.

Arguments

Microsoft's page gives one required argument:

The pinned catalogue supplies NORM.S.INV(probability) with one required argument.

Try it

Quirks

The documented algorithm

Microsoft's page states, unusually explicitly, how the function is computed:

"Given a value for probability, NORM.S.INV seeks that value z such that NORM.S.DIST(z,TRUE) = probability. Thus, precision of NORM.S.INV depends on precision of NORM.S.DIST. NORM.S.INV uses an iterative search technique."

That last sentence is the strongest algorithmic statement Microsoft makes anywhere in the normal family. Three consequences follow from the documentation — the Handbook has not observed Excel and does not assert that the description is current:

1. NORM.S.INV is documented as a root-finder over NORM.S.DIST, not as a direct rational approximation to Φ⁻¹. That is a different architecture from every standard statistical library.
2. Its accuracy is documented as derivative of the forward CDF's, and the conditioning factor 1/φ(z) then amplifies whatever error the CDF carries — most severely at exactly the inputs where users care.
3. Round-trip in p should be near-perfect and monotonicity in z is not guaranteed. A search terminating on |Φ(z) − p| < tol reproduces p by construction while permitting adjacent p values to map to the same or out-of-order z. A direct approximation behaves the other way round. Testing both directions discriminates the two designs, which is the highest-value experiment on this page.

Result and edge cases

Returns Number.

• p = 0.5 returns 0 exactly.
• Logical arguments. TRUE coerces to 1, which is a documented #NUM! boundary, and FALSE coerces to 0, likewise. So both logicals are rejected by the domain rule rather than by a type rule — an example of coercion succeeding and validation then failing.
• The two reachable extremes described above, around −38.5 and +8.13.
• Arrays. The recorded profile is Custom; elementwise lifting is not settled here.

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

Errors

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

A small documentation defect worth recording rather than silently correcting: both error sentences on that page spell the function NORMS.INV — a third spelling that names neither the modern surface (NORM.S.INV) nor the legacy one (NORMSINV). No such function exists. It is a typographical error in the published page, and it is the kind of thing that sends a reader searching for a function that was never shipped.

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

How to implement

Numerical notes

1. Conditioning first. The amplification factor is 1/φ(z), which is 1 at the centre, about 8 at z = 2, about 10⁴ at z = 4, and grows without bound. An error in the input p — including the rounding that put it into a double — is multiplied by that. A correctly rounded NORM.S.INV still returns an answer whose meaning is limited by the representation of p. Accuracy for this function is best stated as a round-trip property.

2. AS 241 is the reference algorithm. Wichura's PPND16 (Applied Statistics 37, 1988) delivers about 16 significant digits across the whole representable domain using three rational approximations chosen by region:

|p − ½| ≤ 0.425                  central branch, in q = p − ½ and q²
otherwise, r = √(−ln min(p,1−p)) tail branches, split again at r ≈ 5

That reparameterization in r is the essential trick: it converts the square-root-of-a-log behaviour into something a rational function can approximate uniformly. R's qnorm, Boost, and most modern libraries descend from it. Weaker predecessors, worth naming because they are still in circulation: A&S 26.2.22 (about 3 × 10⁻³ absolute), A&S 26.2.23 (about 4.5 × 10⁻⁴), Beasley–Springer AS 111 with Moro's tail patch, and Acklam's approximation. None of those is adequate as a reference; all of them are fine for a plot.

3. Newton and Halley refinement close the gap, and reconcile the two designs. With the derivative available in closed form, a single Halley step from a good starting point reaches full double precision:

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

An implementation doing this is consuming the forward CDF, so its accuracy is derivative of the CDF's — exactly what Microsoft's page says about NORM.S.INV. "Iterative search" and "rational approximation plus refinement" are not as far apart as the words suggest; what separates them observationally is monotonicity and the forward-versus-reverse round-trip asymmetry described above.

4. The tails must be computed in the tail parameter, never by symmetry from a central formula. Evaluating Φ⁻¹(p) for tiny p by reflecting a computation of Φ⁻¹(1 − p) throws away everything: 1 − p rounds to 1 and the reflection returns +∞ where a finite answer exists. This is the same complementary-probability trap that runs through the whole distribution family, and it is why p and 1 − p must be kept as separate inputs wherever a surface can offer both.

5. The published assessment record. McCullough and Wilson's series in Computational Statistics & Data Analysis, and Knüsel's assessments of Excel's distribution functions, report on this lane across several Excel versions, with revisions around Excel 2003 and again when the dotted names arrived. Morten Welinder's Gnumeric work is the standard practical account of implementing the normal quantile in a spreadsheet. Named as literature; no figure restated.

Rust code

7 norm_s_inv "NORM.S.INV" (1, 1) [V "probability"] |s, a, sv| {
    out(s, nl::norm_s_inv_kernel(num(s, a, 0)?))
}

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.S.DIST is the forward function and, per Microsoft's text, the thing this function searches over. The round trip is the natural test pair.
• NORM.INV is the general case: NORM.INV(p, μ, σ) = μ + σ·NORM.S.INV(p) mathematically. Whether Excel's two surfaces agree at μ = 0, σ = 1 is unmeasured.
• LOGNORM.INV, STANDARDIZE and Z.TEST are the other members of the normal cluster.
• Confused with: NORM.S.DIST, by readers who want "the z-table" and pick the wrong direction — a z-table is read both ways and only one of these functions reads each way; and with NORM.S.INV(95) where NORM.S.INV(0.95) was meant, which is a #NUM! rather than a quiet wrong answer, mercifully.

Named variants

No named variants are recorded yet.