LOGNORM.INV

What it does

LOGNORM.INV(probability, mean, standard_dev)

LOGNORM.INV(probability, mean, standard_dev) returns the quantile of the lognormal distribution: the value x for which P(X ≤ x) = p.

Because the lognormal CDF is F(x) = Φ((ln x − μ)/σ), inverting is a one-line exercise:

F(x) = p
⇔  (ln x − μ)/σ = Φ⁻¹(p)
⇔  x = exp( μ + σ·Φ⁻¹(p) )

so

LOGNORM.INV(p, μ, σ) = exp( μ + σ·z_p ),   z_p = Φ⁻¹(p)

where Φ⁻¹ is the standard normal quantile function, universally called the probit. The function is therefore an exponential of an affine transform of the probit, and everything interesting about it — its accuracy, its failure modes, its extreme-value behaviour — is inherited from Φ⁻¹.

Domain. 0 < p < 1 strictly, σ > 0, μ any finite real. Range: (0, ∞). The function is strictly increasing in p, with

p → 0⁺  ⇒  x → 0        p → 1⁻  ⇒  x → ∞
p = ½   ⇒  x = e^μ      (the median, independent of σ)

The last of those is worth remembering: at the median the probit is exactly zero, so the answer is exp(μ) regardless of σ. It is the cleanest single test case the function has.

The exact inverse relationship with LOGNORM.DIST holds in mathematics:

LOGNORM.INV( LOGNORM.DIST(x, μ, σ, TRUE), μ, σ ) = x
LOGNORM.DIST( LOGNORM.INV(p, μ, σ), μ, σ, TRUE ) = p

In floating point neither round trip can be exact, and how much they lose is a measurable property of the pair. See Numerical notes.

Quantiles of a lognormal are multiplicative. Since x_p = e^μ · e^{σ z_p}, the ratio of two quantiles depends only on σ: x_{p₂}/x_{p₁} = exp(σ(z_{p₂} − z_{p₁})). That is the structural reason the lognormal is the natural model for quantities that vary by percentages — incomes, particle sizes, failure times, asset prices under geometric Brownian motion, where LOGNORM.INV is precisely the inverse-transform sampler.

Arguments

As on LOGNORM.DIST, mean and standard_dev describe ln X, not X. Feeding this function the sample mean and sample standard deviation of untransformed data is the standard misuse and produces a plausible-looking wrong answer with no diagnostic.

The endpoints are genuinely excluded, not merely awkward. p = 0 would require Φ⁻¹(0) = −∞ and p = 1 would require +∞; neither has a finite answer, so both are domain errors rather than infinities.

Try it

Quirks

Result and edge cases

Returns Number, always strictly positive when it returns at all.

• σ ≤ 0 → #NUM!.
• p extremely small — the probit falls like −√(2·ln(1/p)), so z_p reaches about −38 at the smallest positive normal double and about −39 at the smallest subnormal. The answer is then exp(μ − 38σ), which underflows to zero for quite ordinary σ. A returned zero is a correct underflow, not an error, and it is indistinguishable from a value that genuinely rounds to zero.
• p extremely close to 1 — the mirror case. Here the representation of p is the binding limit, not the algorithm: the doubles just below 1 are spaced 2⁻⁵³ apart, so the largest p distinguishable from 1 is 1 − 2⁻⁵³ and the largest reachable quantile is exp(μ + σ·Φ⁻¹(1 − 2⁻⁵³)) ≈ exp(μ + 8.13σ). Anything beyond that is unreachable through this interface no matter how good the implementation is. This is the reason survival analysis and reliability work want a complementary-probability entry point, and Excel does not offer one for the lognormal.
• σ very large — exp(μ + σ z_p) overflows to #NUM! for |z_p|σ above about 709. The boundary is a legitimate parameter region, and where exactly it falls is unverified.
• Error values in any argument propagate.

Errors

How to implement

Numerical notes

The mathematics is three operations. The accuracy question is entirely about the first.

The probit is the hard part. Φ⁻¹(p) has no closed form. The implementations that matter, in increasing order of accuracy:

• Hastings' rational approximation, published as Abramowitz and Stegun 26.2.22 and 26.2.23, giving about 4.5·10⁻⁴ and 3·10⁻⁴ absolute error respectively. These are trivially cheap and were embedded in an enormous amount of mid-century statistical software. Three or four correct digits.
• Beasley and Springer (AS 111, 1977) with Moro's tail refinement — about seven to nine digits, and the version most often found in quantitative finance code.
• Wichura's AS 241 (1988), the PPND16 routine: two rational approximations in r = √(−ln q) covering the central and tail regions, delivering close to full double precision across the representable range. This is the modern reference and what a careful implementation uses.
• Newton or Halley refinement of any of the above against a high-quality Φ, which converges quadratically or cubically and can polish a nine-digit start to full precision in one or two steps — at the cost of needing an accurate forward Φ and its derivative.

The published critique record on spreadsheet statistics is directly relevant here: Knüsel's assessments of statistical distribution accuracy, and McCullough and Wilson's On the accuracy of statistical procedures in Microsoft Excel, both singled out the inverse normal as a low-accuracy routine in the Excel versions they examined, and tracked changes across releases. Morten Welinder's work on Gnumeric's statistical functions covers the same ground from the reimplementer's side. The Handbook does not assert what any Excel build computes internally, and it has run none of those tests itself.

Error propagation through the exponential. Let δ be the absolute error in z_p. Then

x̂ = exp(μ + σ(z_p + δ)) = x·exp(σδ)  ⇒  relative error ≈ σ·δ

So the absolute error of the probit becomes a relative error of the result, amplified by σ. A probit good to 3·10⁻⁴ — the Hastings level — gives a lognormal quantile good to about 0.03% when σ = 1, and to about 1.5% when σ = 50. That is the entire argument for using AS 241 rather than a cheap rational fit, stated quantitatively.

Where it is worst. Both the probit's own difficulty and the amplification factor grow in the tails: z_p is steep near the endpoints (dz/dp = 1/φ(z), which blows up), and σ|z_p| is large exactly there. The far tail of LOGNORM.INV is therefore doubly disadvantaged, and it is also where simulation users spend their time.

What a careful implementation does. Use AS 241 or an equivalently accurate probit; compute μ + σ z_p in one fused step if available, since the addition can cancel when μ and σz_p have opposite signs and comparable size; and use a correctly rounded exp (fdlibm's __ieee754_exp and descendants). Do not compute the quantile by bisecting the forward CDF — a common shortcut that inherits the forward function's tail saturation and cannot reach the extremes at all.

Rust code

11 lognorm_inv "LOGNORM.INV" (3, 3) [V "probability", V "mean", V "standard_dev"] |s, a, sv| {
    let [p, m, sd_] = nums::<3>(s, a)?;
    out(s, nl::lognorm_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

• LOGNORM.DIST — the forward distribution, and the exact inverse. Note the argument-count asymmetry: the forward surface takes a cumulative flag and the inverse does not, because only the CDF can be inverted. A density has no quantile.
• LOGINV — the legacy spelling, retained in the Compatibility category with the same three arguments. The documented relationship is that LOGINV(p, μ, σ) and LOGNORM.INV(p, μ, σ) compute the same quantity. Documented equivalence is not demonstrated identity of computation. The two are separate registered surfaces and may reach the answer by different code; establishing that they return the same bits requires evidence, and the Handbook has none for either surface. This is the cleanest legacy/modern pair in the family to test, because the signatures are identical.
• NORM.S.INV deserves separate mention as the accuracy bottleneck: every digit LOGNORM.INV can deliver comes from the probit.
• RAND — LOGNORM.INV(RAND(), μ, σ) is the standard inverse-transform lognormal sampler, and the reason the extreme-tail behaviour above is not academic: a simulation with a million draws will visit p values within 10⁻⁶ of both endpoints.
• Confused with: the mean and standard deviation of the data rather than of its logarithm; and with LOGNORM.DIST(..., FALSE), which is a density and not an inverse of anything.

Named variants

No named variants are recorded yet.