LOGNORM.DIST

What it does

LOGNORM.DIST(x, mean, standard_dev, cumulative)

LOGNORM.DIST(x, mean, standard_dev, cumulative) evaluates the lognormal distribution.

A positive random variable X is lognormal with parameters μ and σ when ln X is normal with mean μ and standard deviation σ. The two functions follow by change of variable:

Density (cumulative = FALSE):

f(x; μ, σ) = 1 / ( x·σ·√(2π) ) · exp( −(ln x − μ)² / (2σ²) ),    x > 0

Distribution function (cumulative = TRUE):

F(x; μ, σ) = Φ( (ln x − μ) / σ ),    x > 0

where Φ is the standard normal CDF. That second identity is the entire mathematical content of the function: the lognormal CDF is the normal CDF of the standardised logarithm. The 1/x factor in the density is the Jacobian of the substitution u = ln x, and it is the only place the two forms differ structurally.

The parameters are those of ln X, not of X. This is the single most common misreading of this function, and it is worth setting the record out explicitly:

So mean = 0, standard_dev = 1 describes a distribution whose median is 1 and whose mean is √e ≈ 1.6487. Passing the sample mean and standard deviation of the data into this function gives an answer to a different question. The correct parameter estimates are the mean and standard deviation of the logged data.

Domain. x > 0, σ > 0, μ any real. Range: the density is positive and unbounded above as σ → 0; the CDF is confined to (0, 1), is strictly increasing on (0, ∞), and satisfies F(0⁺) = 0, F(∞) = 1. The distribution is right-skewed for every σ > 0, with skewness (e^{σ²} + 2)·√(e^{σ²} − 1), and all its moments are finite even though its moment-generating function does not exist — a standard textbook curiosity, and the reason the lognormal is not determined by its moments.

The two forms are related by differentiation, f = F′, and a correct implementation should satisfy that relation numerically to within the accuracy of Φ and ln; it is a usable self-check.

Arguments

All four are scalar slots governed by ordinary to-number and to-logical coercion — see Coercion and lifting.

Try it

Quirks

Result and edge cases

Returns Number.

• x → 0⁺ — the CDF tends to zero and the density tends to zero as well, because exp(−(ln x)²/2σ²) beats the 1/x blow-up. The density therefore has a removable-looking but genuinely zero limit at the origin, which is why the lognormal density is smooth at 0 even though the formula has a pole there.
• Very large x — the CDF approaches 1 and, in double precision, reaches it: once (ln x − μ)/σ exceeds roughly 8.3, Φ rounds to exactly 1.0 and every distinction in the upper tail is lost. See Numerical notes; this is a representational limit, not an implementation defect, and it is the reason there is no way to compute a small upper-tail probability from this function.
• Very small x — the CDF underflows to zero once the standardised value falls below roughly −38, and the density underflows sooner because of the extra factor.
• σ very small — the distribution concentrates at e^μ; the density there behaves like 1/(e^μ σ√(2π)) and overflows for σ small enough. A density that overflows to #NUM! on a legitimate parameter is a real edge, and where the boundary falls is unverified.

Errors

How to implement

Numerical notes

LOGNORM.DIST performs almost no arithmetic of its own. Its accuracy is the accuracy of two imported primitives, and the interesting engineering is entirely in how they are used.

The composition. The cumulative form is Φ(z) with z = (ln x − μ)/σ. Error enters at three points:

1. ln x — a correctly rounded logarithm gives z a relative error of about one ulp in the logarithm, which becomes an absolute error in z of about |ln x|·ε/σ. For large x and small σ this is amplification: x = 10¹⁰⁰ gives ln x ≈ 230, so an ulp of the logarithm is around 3·10⁻¹⁴, divided by σ.
2. The subtraction ln x − μ — cancellation when ln x ≈ μ, i.e. near the median. The relative error of z there is unbounded, though the absolute error stays small, and since Φ is Lipschitz with constant 1/√(2π) near zero the CDF value is still accurate in absolute terms. This is the usual and acceptable trade.
3. Φ(z) — the normal CDF, whose own accuracy is the subject of a long literature.

The upper-tail problem. Φ(z) → 1 and there is no way back. In double precision Φ(z) = 1.0 exactly for z ≳ 8.3, and long before that the returned value has no significant digits in 1 − Φ(z). Any calculation that needs a small exceedance probability — value-at-risk, reliability, tail pricing — must not compute 1 − LOGNORM.DIST(...). The mathematically correct route uses the complement directly, 1 − Φ(z) = Φ(−z) = ½·erfc(z/√2), which is accurate to full relative precision for z out to about 37 before erfc itself underflows. Excel exposes no complementary lognormal CDF, so the workaround inside the worksheet is LOGNORM.DIST(1/x, −μ, σ, TRUE), using the reflection identity: if X is lognormal (μ, σ) then 1/X is lognormal (−μ, σ). That identity is exact and gives back the upper tail as a lower tail. It is the single most useful practical fact on this page.

The reference literature. For Φ and erfc: Abramowitz and Stegun chapter 26, whose formulas 26.2.16 to 26.2.19 are the classical series and continued-fraction expansions and whose 26.2.17 rational approximation is the one embedded in an enormous amount of legacy code at about seven decimal digits; Cody's rational Chebyshev approximations for erf/erfc (Rational Chebyshev approximation for the error function, Math. Comp. 1969) and his ALGORITHM 715 / SPECFUN implementations, which are the accuracy standard; Hart et al., Computer Approximations; and the treatments in Boost.Math and Cephes. For ln: fdlibm's __ieee754_log and its glibc and musl descendants; Muller, Elementary Functions, for the argument-reduction theory.

The published critique record on Excel's statistical distributions — McCullough and Wilson's accuracy papers, Knüsel's assessments of statistical distribution accuracy in spreadsheets, and Morten Welinder's work on Gnumeric's statistical functions — is the relevant background for this family. The Handbook names it as literature and does not assert what Excel computes internally.

A note on the density. f(x) should be evaluated as exp(−z²/2) / (x·σ·√(2π)), not by exponentiating a difference of logarithms, and the √(2π) constant should be a correctly rounded literal rather than computed as sqrt(2*PI()). For very negative −z²/2 the exponential underflows gradually; returning a subnormal is better than returning zero, and whether that happens is observable.

Rust code

9 lognorm_dist "LOGNORM.DIST" (4, 4) [V "x", V "mean", V "standard_dev", V "cumulative"] |s, a, sv| {
    let [x, m, sd_] = nums::<3>(s, a)?;
    out(s, nl::lognorm_dist_kernel(x, m, sd_, flag(s, a, 3)?))
}

No implementation has been published with a Handbook test suite for this function.

Version differences

The recorded environments below distinguish a reading from a same-input comparison. Counts describe captured inputs in each environment; overlapping captures and comparisons are not totals of unique worksheet calls.

The record contains no same-input comparison between these environments. Reading different inputs on different builds does not establish either a version change or cross-build agreement.

Recorded environments and comparison details.

Alternatives

Relationships

• LOGNORMDIST — the legacy three-argument, cumulative-only spelling, retained in the Compatibility category. The documented relationship is LOGNORMDIST(x, μ, σ) = LOGNORM.DIST(x, μ, σ, TRUE). That is an equality of documented meaning, not a demonstrated equality of computation. Two surfaces that document the same value may be reached by different code with different rounding; proving they return the same bits requires evidence, and the Handbook has none. Both of the evidence records attached to this page treat LOGNORM.DIST and LOGNORMDIST as an unresolved pair for exactly this reason — one of them notes that the upstream sentence carrying the only relevant figure does not say which of the two spellings was probed.
• LOGNORM.INV — the quantile function, and the exact inverse in the mathematical sense: LOGNORM.INV(LOGNORM.DIST(x, μ, σ, TRUE), μ, σ) = x. The round trip is the natural metamorphic probe for both surfaces.
• WEIBULL.DIST, GAMMA.DIST — the other right-skewed positive-support distributions readers choose between. The lognormal is the one with the multiplicative central-limit justification: a product of many independent positive factors tends to lognormal, as a sum tends to normal.
• Confused with: the normal distribution of the data itself, and — persistently — with the meaning of the mean argument. See above.

Named variants

No named variants are recorded yet.