LOGNORM.DIST Function

Returns the lognormal distribution of a value.

Category

Statistical

Available in

Excel 2010+, Microsoft 365

Returns

A number

Want to practice LOGNORM.DIST in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

A lognormal distribution describes positive values whose logarithms are bell-shaped — typical for order values, incomes, house prices and task durations: most values are modest, with a long tail of large ones.

mean and standard_dev are the mean and standard deviation of ln(x), not of x itself. Find them with =AVERAGE(LN(range)) and =STDEV.S(LN(range)).

TRUE returns the cumulative probability (the chance of a value up to x); FALSE returns the probability density — the height of the curve, not a probability.

Syntax

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

Arguments

ArgumentRequired?What it does
xYesThe value; must be greater than 0.
meanYesThe mean of ln(x).
standard_devYesThe standard deviation of ln(x); must be greater than 0.
cumulativeYesTRUE for the cumulative probability, FALSE for the density (height of the curve).

Examples

Example 1 — Orders of $100 or less

Northstar's order values have ln-mean 4 and ln-standard-deviation 0.5 — a median order of about $55.

B5fx=LOGNORM.DIST(B4,B2,B3,TRUE)
AB
1MeasureValue
2ln mean4
3ln std dev0.5
4Order value100
5P($100 or less)0.8869
=LOGNORM.DIST(B4,B2,B3,TRUE) Explain

About 89% of orders are $100 or less; 11% are larger.

Common mistakes

  • Using the plain mean. mean and standard_dev must describe ln(x). Using the average order value gives nonsense.
  • #NUM! x is 0 or negative, or standard_dev is 0 or negative.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.