Triangular distribution in Excel: formula, mean and how it compares with PERT
In short: the simplest way to turn a low, a most likely and a high value into odds. The chance rises in a straight line up to the most likely value and falls in a straight line down to the high one.
A triangular distribution turns a three-point estimate into odds that rise in a straight line from the minimum to the most likely value and fall in a straight line to the maximum. Its mean is (min + most likely + max) / 3: for a cost estimated at 80, 100 and 140 (USD thousands) that is 106.7, and its P90 is 124.5. Excel has no triangular function, but one formula with RAND() and SQRT draws from it.
What a triangular distribution is
Three numbers define it: a minimum below which nothing happens, a maximum above which nothing happens, and the most likely value, where the density peaks. Between them the density is two straight lines, which makes it the simplest way to turn an expert’s low, most likely and high into a full range of outcomes.
Formulas: mean, standard deviation, median
| Quantity | Formula and example |
|---|---|
| Mean | (min + ml + max) / 3 = 320 / 3 = 106.7 |
| Standard deviation | √((min² + ml² + max² − min × ml − min × max − ml × max) / 18) = 12.5 |
| Chance below the most likely value | (ml − min) / (max − min) = 20/60 = 33.3% |
| Median (P50) | 105.4 |
With the most likely value nearer the minimum, the long side is above it: two thirds of outcomes exceed the most likely cost, and the mean, 106.7, lies well above it. That is why a budget that adds up most likely values is usually too low.
The triangular distribution in Excel
Excel’s RAND() gives a random number u between 0 and 1, and a formula turns it into a value of the triangle: a small u gives a value near the minimum, a large u one near the maximum, spaced so that each part of the range gets its fair share of draws. For u below the share under the most likely value, the value is min + √(u × (max − min) × (ml − min)); otherwise it is max − √((1 − u) × (max − min) × (max − ml)). The formula uses u twice, so u needs a cell of its own: two separate RAND() calls would give two different numbers and a wrong distribution. In the workbook, u is in I7 and the draw in J7:
| Cell | Formula | Value |
|---|---|---|
| Random number u (I7) | =RAND() | |
| Draw (J7) | =IF(I7<(F7-D7)/(H7-D7), | |
| Mean (K7) | =(D7+F7+H7)/3 | 106.7 |
| P10 (L7) | =IF(0.1<(F7-D7)/(H7-D7), | 91.0 |
| P90 (M7) | =IF(0.9<(F7-D7)/(H7-D7), | 124.5 |
Check the numbers: exact values against 10,000 draws from Excel and from xellstorm
| Exact | Excel, RAND() | xellstorm | |
|---|---|---|---|
| Mean | 106.7 | 106.7 | 106.7 |
| Standard deviation | 12.5 | 12.6 | 12.5 |
| P10 | 91.0 | 90.6 | 91.0 |
| P50 (median) | 105.4 | 105.6 | 105.4 |
| P90 | 124.5 | 124.7 | 124.5 |
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.
Replace u by a fixed probability to get a percentile without simulating: 0.9 gives the P90, 124.5. Both sets of draws in the table reproduce the exact values.
Triangular vs PERT: same three numbers, different weight on the limits
A PERT distribution takes the same minimum, most likely value and maximum but draws a smooth curve between them. The workbook has both, in rows 7 and 8:
How to read the chart: the higher a line, the more likely the values beneath it. Both shapes start at the minimum, peak at the most likely value and end at the maximum; the triangular stays higher near both ends.
| Triangular | PERT | |
|---|---|---|
| Mean | 106.7 | 103.3 |
| Standard deviation | 12.5 | 11.1 |
| P10 | 91.0 | 89.2 |
| P90 | 124.5 | 118.7 |
| Chance above 134 | 1.5% | 0.1% |
The triangular keeps more weight near the limits: it gives a 1.5% chance of a cost above 134, the PERT 0.1%. Its mean counts the most likely value once rather than four times, so on a skewed estimate it lands higher (106.7 against 103.3), and its P90 is higher too (124.5 against 118.7). Neither is more correct; they read the same three numbers differently.
- Choose triangular when values near the limits are realistic, or when you want a more cautious spread from the same estimate.
- Choose PERT when the most likely value is what you trust most and the limits are bounds you do not expect to reach.
- Choose neither when the low and high are not limits but one-in-ten cases (P10 and P90). Used as min and max, they leave out the 20% of outcomes beyond them; fit a distribution to the percentiles instead, or choose Triangular from percentiles, a triangular set by them.
Entering a triangular distribution in xellstorm
- Make the cell an input and, on the Distributions step, choose Triangular. Type its min, likely and max; the Mean and P10 – P90 columns update as you type.
- If your low and high are P10 and P90, choose Triangular from percentiles instead and enter them as bottom and top with bottom % 10 and top % 90, or select the input and fit a shape to them with “From estimates” in the side panel. Not every set of percentiles fits a triangular exactly; the panel shows how far the closest one misses.
- To compare readings of the same estimate, add a scenario that changes the input to PERT. Scenarios run on the same random draws as the base case, so the difference comes from the shape alone.
Add-in models convert from RiskTriang, VoseTriangle and PsiTriangular, and RiskTrigen converts to Triangular from percentiles.
Questions
How do I generate a triangular distribution in Excel?
Put a random number in one cell, =RAND(), and turn it into a draw in another with the inverse formula. With the random number in I7, min in D7, most likely in F7 and max in H7: =IF(I7<(F7-D7)/(H7-D7),D7+SQRT(I7*(H7-D7)*(F7-D7)),H7-SQRT((1-I7)*(H7-D7)*(H7-F7))). The workbook on this page has it ready to copy.
What is the mean of a triangular distribution?
The mean is (min + most likely + max) / 3. For an estimate of 80, 100 and 140, it is 320 / 3 = 106.7. A PERT with the same numbers has the mean (min + 4 × most likely + max) / 6 = 103.3.
How do I get the P90 of a triangular distribution?
Use the draw formula with 0.9 in place of the random number: =IF(0.9<(F7-D7)/(H7-D7),D7+SQRT(0.9*(H7-D7)*(F7-D7)),H7-SQRT((1-0.9)*(H7-D7)*(H7-F7))) gives 124.5 for an estimate of 80, 100 and 140. This is the P90 of one item; for a total of several items run a simulation, because percentiles do not add up.
Is triangular or PERT more conservative?
For the same three numbers, the triangular spreads wider and puts more weight near the limits. On a right-skewed estimate its mean and its P90 are both higher, so it is the more cautious reading. On a symmetric estimate the means are equal, and the triangular is wider on both sides.
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
PERT distribution in Excel: formula, mean and when to use it
A PERT is a beta distribution from min to max with mean (min + 4 × likely + max)/6. The Excel BETA.INV formula, and PERT vs triangular. - Guide
Uniform distribution in Excel: random numbers between two values
A uniform distribution makes every value between a min and a max equally likely. The Excel formula, RANDBETWEEN for whole numbers, its mean and when a range alone is enough.
xellstorm is a browser-based Monte Carlo simulation tool for Excel models: no add-in, and the workbook never leaves your computer.