Normal distribution in Excel: NORM.INV, mean and standard deviation
In short: the bell curve. Most outcomes land close to the average, and the further a value is from it, the rarer it gets, equally on both sides.
A normal distribution describes a quantity that varies symmetrically around its mean, with a spread set by its standard deviation. In Excel, =NORM.INV(RAND(), mean, sd) draws one value from it. For a demand with mean 1,000 units and standard deviation 200, the P10 is 744 and the P90 1,256, and about 95% of outcomes fall within two standard deviations: 600 to 1,400.
What a normal distribution is
The normal distribution is the bell curve: most outcomes lie near the mean, fewer the further away they are, and the two sides mirror each other. Two numbers define it completely. The mean sets the centre, which is also the median and the most likely value. The standard deviation sets the width: about two thirds of outcomes lie within one standard deviation of the mean.
How to read the chart: each bar counts how many of the 10,000 simulated values fell in that stretch, and the line is the theoretical curve. The taller the bar, the more often values land there. The dotted lines mark the P10 and the P90: eight outcomes in ten fall between them.
| Range | Demand | Share of outcomes |
|---|---|---|
| Mean ± 1 standard deviation | 800 – 1,200 | 68.3% |
| Mean ± 2 standard deviations | 600 – 1,400 | 95.4% |
| Mean ± 3 standard deviations | 400 – 1,600 | 99.7% |
Percentiles follow from the same two numbers: the P90 lies 1.2816 standard deviations above the mean and the P10 the same distance below it, so 80% of outcomes fall between 744 and 1,256.
The normal distribution in Excel
NORM.INV(probability, mean, standard_deviation) returns the value below which the given share of outcomes falls. With a fixed probability it gives a percentile; with RAND() as the probability it gives a random draw, a new one each time the sheet recalculates. The workbook on this page keeps the random number in a cell of its own, so you can see which draw came from which number; in one cell the draw is =NORM.INV(RAND(),D5,F5).
| Cell | Formula | Value |
|---|---|---|
| Random number u (I5) | =RAND() | |
| Draw (J5) | =NORM.INV(I5, | |
| Draw in one cell | =NORM.INV(RAND(), | |
| Mean (K5) | =D5 | 1,000 |
| P10 (L5) | =NORM.INV(0.1, | 744 |
| P90 (M5) | =NORM.INV(0.9, | 1,256 |
Check the numbers: exact values against 10,000 draws from Excel and from xellstorm
| Exact | Excel, RAND() | xellstorm | |
|---|---|---|---|
| Mean | 1,000 | 998 | 1,000 |
| Standard deviation | 200 | 199 | 200 |
| P10 | 744 | 743 | 744 |
| P50 (median) | 1,000 | 996 | 1,000 |
| P90 | 1,256 | 1,251 | 1,256 |
Excel draws with RAND(), fully at random; xellstorm spreads its draws evenly over the range (Latin Hypercube sampling), so its statistics land closer to the exact values.
The opposite question, how likely demand is to stay below a level, is NORM.DIST(x, mean, standard_deviation, TRUE): =NORM.DIST(700, 1000, 200, TRUE) gives 6.7%, the chance of a season below 700 units. The older names NORMINV and NORMDIST still work. One draw is just one possible season; to see what the uncertainty does to a profit or a stock level, a model needs thousands of them, which is what a Monte Carlo simulation does (see Monte Carlo simulation in Excel).
When the normal distribution fits
- Measurements around a target. Machined dimensions, fill weights and measurement errors vary symmetrically around a nominal value, as in the tolerance stack-up example.
- Totals of many independent parts. Add up many small uncertainties that do not depend on each other, and the total looks like a bell curve, whatever the shape of each part. Statisticians call this the central limit theorem.
- Demand of an established product whose standard deviation is small compared with its mean, like the demand in the order-quantity example.
When it does not
A normal distribution has no lower limit: it gives negative values with some probability. For the demand on this page that chance is negligible, but with a standard deviation of 500, half the mean, 2.3% of draws would be negative demand. For quantities that cannot be negative and spread widely, use a lognormal or a gamma distribution, or truncate the normal at zero.
It is also symmetric. Costs and durations usually overrun by more than they underrun; a PERT, triangular or lognormal distribution carries that skew. And it makes extremes very rare: values more than three standard deviations from the mean almost never occur, which understates the big moves of prices and returns. Student’s t gives extreme values more often.
Entering a normal distribution in xellstorm
- Open the workbook, click the cell you are unsure of and choose Make input.
- On the Distributions step, choose Normal and type the mean and the sd (standard deviation). The Mean and P10 – P90 columns show what the two numbers give before you run.
- If you think in a low, typical and high value rather than a standard deviation, select the input and use “From estimates” in the side panel: type your P10, P50 and P90 and choose Normal. With past values, “From data” fits a normal and other shapes to them and ranks them.
- To keep draws above zero, open “Truncate / shift / scale” under the parameters and set a truncate min of 0.
A workbook that already draws with =NORM.INV(RAND(), mean, sd) needs no rewriting: “Import from workbook” turns such a cell into a normal input, with the mean and standard deviation still linked to their cells. Models built for an add-in convert the same way from RiskNormal, VoseNormal and PsiNormal.
Questions
How do I generate normally distributed random numbers in Excel?
Use =NORM.INV(RAND(), mean, sd), for example =NORM.INV(RAND(), 1000, 200) for a mean of 1,000 and a standard deviation of 200. Each recalculation (F9) draws a new value. Copy the formula down a column for many draws; their average approaches the mean and their standard deviation approaches the one you entered.
What is the difference between NORM.INV and NORM.DIST?
They answer opposite questions. NORM.DIST(x, mean, sd, TRUE) takes a value and returns the probability of an outcome below it; NORM.INV(p, mean, sd) takes a probability and returns the value with that share of outcomes below it. For demand with mean 1,000 and standard deviation 200, NORM.INV(0.9, 1000, 200) is 1,256, and NORM.DIST of that value returns 0.9.
How do I find the P90 of a normal distribution?
The P90 is the mean plus 1.2816 standard deviations, or in Excel =NORM.INV(0.9, mean, sd): 1,256 for a mean of 1,000 and a standard deviation of 200. The P10 is the same distance below the mean, 744, and the P95 is 1,329. These are percentiles of one input; the P90 of a total of several inputs needs a simulation, because percentiles do not add up (see P50, P80 and P90).
Can a normal distribution give negative values?
Yes. A normal distribution extends without limit in both directions, so it always gives negative values with some probability. When the mean is more than about four standard deviations above zero, that probability is tiny; when the standard deviation is half the mean, it is 2.3%. For amounts that cannot be negative, truncate the distribution at zero or use a lognormal or gamma distribution.
Related
- Guide
Which probability distribution should I use? A guide for Monte Carlo models
Find the right probability distribution in two questions: PERT, triangular, normal, lognormal, Weibull, Poisson, binomial, beta and more, with what each is for and its Excel formula. - Guide
Lognormal distribution in Excel: LOGNORM.INV, parameters and when to use it
A lognormal distribution is positive with a long right tail. How LOGNORM.INV’s parameters work, how to get them from a mean and standard deviation, and when to use it. - Tolerance stack-up
Tolerance stack-up analysis: worst case, RSS or Monte Carlo?
A housing and five parts: worst case lets the gap shrink to 0.120 mm; RSS predicts 3.29% of gaps below 0.4 mm and 20,000 Monte Carlo trials find 3.21%.
xellstorm is a browser-based Monte Carlo simulation tool for Excel models: no add-in, and the workbook never leaves your computer.