NORMINV

What it does

NORMINV(probability, mean, standard_dev)

NORMINV(probability,mean,standard_dev) is the compatibility normal quantile: it returns the value with the requested cumulative probability for the supplied mean and positive standard deviation.

Try it

=NORMINV(0.5,10,2) returns 10. The median of this symmetric distribution equals its mean.

Quirks

The probability must be strictly between zero and one; endpoints or out-of-range values give #NUM!. Nonnumeric arguments give #VALUE!. A nonpositive standard deviation also gives #NUM!; the mean may be any admitted numeric location.

A small probability refers to the far left tail. For an upper-tail cutoff use the corresponding cumulative probability, while recognizing that 1-small_tail can round to one for very small tails. Supply probability as a number, such as 0.5 or a numeric 50% cell.

Microsoft describes an iterative inverse search over the forward normal CDF, with #N/A if it has not converged after 100 iterations on this legacy page. That documented method and limit do not identify the current candidate’s rational coefficients or numerical stopping stages.

How to implement

The current standard-normal inverse is a published-method candidate:

1. Validate 0<p<1. Choose its rational approximation branch at p=0.02425 and p=1-0.02425.
2. In the centre, set q=p-0.5, r=q*q and evaluate the coefficient polynomials in r, multiplying the numerator by q. In the lower tail use q=sqrt(-2*crt_log(p)); in the upper tail use sqrt(-2*crt_log(1-p)) and reverse the sign. The cited source retains the coefficients and their Horner order.
3. Refine the candidate z once. Form a small tail directly as tail=0.5*erfc(abs(z)/sqrt(2)), using the current library complementary error function. Set err=tail-p for negative z, or err=(1-p)-tail otherwise.
4. If the normal density at z is nonzero, replace z by z-err/density. If it is zero, keep the rational candidate.

Return the current binary64 combination mean+sigma*z, keeping multiplication and addition separate. At p=0.5, the central numerator has q=0, and the symmetric answer is zero, so the affine result is the supplied mean.

This recipe can be coded from the source’s published-approximation coefficients, but it is not an identified Excel inverse. In particular, its Newton tail is a library erfc call; substituting the public worksheet ERFC introduces another numerical candidate. The unknown Excel approximation and stopping/rounding stages remain open.

Rust code

4 norminv "NORMINV" (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 same-input comparison between named Excel builds is recorded for this function here. The documented behaviour below is not a measurement of every Excel release.

Alternatives

NORM.INV is the modern spelling; NORMDIST gives the forward probability.

Named variants

No named variants are recorded yet.