Uniform distribution in Excel: random numbers between two values
In short: every value between a minimum and a maximum is equally likely, like a dice roll stretched over a range.
A uniform distribution makes every value between a minimum and a maximum equally likely. In Excel, =min + (max - min) * RAND() draws from it, and RANDBETWEEN(min, max) draws whole numbers. For a unit price between 40 and 60 (USD), the mean is 50, the P10 42 and the P90 58.
What a uniform distribution is
Two numbers define it: the lowest and the highest possible value. In between, the density is flat, so any stretch of the range gets a share of outcomes in proportion to its width: with a range of 20, each unit of it gets 5.0% of the outcomes, and the P10 lies a tenth of the way from the minimum.
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. Here the bars are all about the same height: no part of the range is favoured.
Formulas: mean, standard deviation, percentiles
| Quantity | Formula and example |
|---|---|
| Mean and median | (min + max) / 2 = 50 |
| Standard deviation | (max − min) / √12 = 20 / 3.464 = 5.77 |
| Percentile p | min + p × (max − min) P10 = 42, P90 = 58 |
The uniform distribution in Excel
RAND() itself is uniform between 0 and 1. Stretching it onto any range gives a uniform draw: in one cell, =D9+(F9-D9)*RAND(), with the minimum in D9 and the maximum in F9. For whole numbers, RANDBETWEEN(min, max) returns an integer from min to max, both included.
| Cell | Formula | Value |
|---|---|---|
| Random number u (I9) | =RAND() | |
| Draw (J9) | =D9+(F9-D9)*I9 | |
| Draw in one cell | =D9+(F9-D9)*RAND() | |
| Mean (K9) | =(D9+F9)/2 | 50 |
| P10 (L9) | =D9+(F9-D9)*0.1 | 42 |
| P90 (M9) | =D9+(F9-D9)*0.9 | 58 |
Check the numbers: exact values against 10,000 draws from Excel and from xellstorm
| Exact | Excel, RAND() | xellstorm | |
|---|---|---|---|
| Mean | 50.00 | 49.96 | 50.00 |
| Standard deviation | 5.77 | 5.78 | 5.77 |
| P10 | 42.00 | 41.97 | 42.00 |
| P50 (median) | 50.00 | 49.91 | 50.00 |
| P90 | 58.00 | 57.95 | 58.00 |
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.
When a uniform distribution fits
Use it when you know a range and nothing more: no value inside it is more plausible than another. That is rarer than it seems. If you would bet on the middle rather than on the edges, a triangular or PERT distribution says so. On the same range from 40 to 60, a triangular peaked at 50 puts 75.0% of outcomes between 45 and 55; the uniform puts only 50.0% there, and its standard deviation is 5.77 against 4.08.
- Good uses: a value that lands anywhere in a known window (an arrival within an hour, a position along a part), a parameter you want to sweep across a plausible range to see how much it matters, or a first draft of a model before better estimates exist.
- Poor uses: costs and durations, where the extremes are less likely than the middle, and anything without hard limits.
Entering a uniform distribution in xellstorm
- Make the cell an input and, on the Distributions step, choose Uniform; type its min and max. For whole numbers choose Integer uniform.
- With past values, “From data” in the side panel compares a uniform with other shapes, so you can see whether the data really are flat.
“Import from workbook” converts cells such as =a + (b - a)*RAND() into uniform inputs and RANDBETWEEN(a, b) into integer uniform ones, and add-in models convert from RiskUniform, VoseUniform and PsiUniform.
Questions
How do I generate a random number between two values in Excel?
Use =min + (max - min) * RAND(), for example =40 + (60 - 40) * RAND() for a value between 40 and 60. For whole numbers, use =RANDBETWEEN(40, 60). Both draw again whenever the sheet recalculates.
What are the mean and standard deviation of a uniform distribution?
The mean is the midpoint, (min + max) / 2, and the standard deviation is (max − min) / √12, about 0.29 times the width. From 40 to 60: mean 50, standard deviation 5.77.
Uniform or triangular?
Use uniform when no value in the range is more likely than another; use triangular, or PERT, when you have a most likely value. The uniform is the more spread-out choice for the same range: its standard deviation is 5.77 against 4.08 for a triangular peaked in the middle.
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
Triangular distribution in Excel: formula, mean and how it compares with PERT
A triangular distribution turns min, most likely and max into straight-line odds. The Excel formula for random draws and percentiles, its mean, and triangular vs PERT. - Guide
Monte Carlo simulation in Excel, with or without add-ins
Three ways to run a Monte Carlo simulation on an Excel model: RAND() and a data table, an add-in, or a browser tool with no add-in. PERT formula included.
xellstorm is a browser-based Monte Carlo simulation tool for Excel models: no add-in, and the workbook never leaves your computer.