Binomial and Bernoulli distributions in Excel: yes-or-no events and counts out of n
In short: a Bernoulli distribution answers “does it happen?” with 1 or 0; a binomial answers “how many out of n?”.
A Bernoulli distribution is a single yes-or-no event: a risk that occurs (1) with probability p or does not (0), drawn in Excel with =IF(RAND() < p, 1, 0). A binomial distribution counts the yeses among n independent tries with the same probability, drawn with =BINOM.INV(n, p, RAND()). In a batch of 50 units with a 4% defect rate, 2 are defective on average, a batch has no defect with a 13.0% chance and 5 or more with 4.9%.
Bernoulli: does it happen or not?
Many uncertainties in a model are events rather than amounts: a permit is delayed or not, a supplier fails or not, a risk in a register occurs or not. A Bernoulli distribution gives 1 with probability p and 0 otherwise. Multiply it by the impact and you have the risk’s cost in each trial: =IF(RAND() < 0.3, 1, 0) * 500 is 500 in about 30% of trials and 0 in the rest.
| Cell | Formula | Value |
|---|---|---|
| Random number u (I14) | =RAND() | |
| Draw (J14) | =IF(I14<D14, | |
| Draw in one cell | =IF(RAND()<D14, | |
| Mean (K14) | =D14 | 0.3 |
| P10 (L14) | =BINOM.INV(1, | 0 |
| P90 (M14) | =BINOM.INV(1, | 1 |
Its mean is p, so the expected cost of that risk is 0.3 × 500 = 150. But no trial ever costs 150: each costs 0 or 500. Putting probability × impact into a model as a fixed number hides exactly this, which is why a register of such risks needs a simulation to size its contingency (see the risk register example). In xellstorm’s 10,000 draws of this row, the risk occurred in 30.0% of trials.
Binomial: how many out of n?
Repeat the same yes-or-no chance n times, independently, and count the yeses: that count is binomial. Defective units in a batch, customers out of 200 who accept an offer, days out of 20 with rain. Its mean is n × p and its standard deviation √(n × p × (1 − p)): 2 and 1.39 for a batch of 50 with p = 0.04. A Bernoulli is a binomial with n = 1.
How to read the chart: each bar is the share of the 10,000 simulated batches with that many defective units, and each dot the exact probability.
| Cell | Formula | Value |
|---|---|---|
| Random number u (I15) | =RAND() | |
| Draw (J15) | =BINOM.INV(D15, | |
| Draw in one cell | =BINOM.INV(D15, | |
| Mean (K15) | =D15*F15 | 2 |
| P10 (L15) | =BINOM.INV(D15, | 0 |
| P90 (M15) | =BINOM.INV(D15, | 4 |
Check the numbers: exact values against 10,000 draws from Excel and from xellstorm
| Exact | Excel, RAND() | xellstorm | |
|---|---|---|---|
| Mean | 2 | 2.01 | 2.00 |
| Standard deviation | 1.39 | 1.40 | 1.39 |
| P10 | 0 | 0 | 0 |
| P50 (median) | 2 | 2 | 2 |
| P90 | 4 | 4 | 4 |
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 binomial distribution in Excel
BINOM.DIST(k, n, p, FALSE)is the chance of exactly k: two defective units in the batch has a 27.6% chance. WithTRUEit is the chance of k or fewer, so=1-BINOM.DIST(4, 50, 0.04, TRUE)gives the 4.9% chance of 5 or more.BINOM.INV(n, p, probability)answers the opposite question: the smallest count that is not exceeded with at least the given probability, a percentile. WithRAND()it draws a random count:=BINOM.INV(D15,F15,RAND()). Note the order: the probability comes last, unlikeNORM.INV. The older name isCRITBINOM.
With many tries and a small probability, the binomial is close to a Poisson distribution with the same mean: a batch without defects has a 13.0% chance under the binomial and 13.5% under a Poisson with mean 2.
Entering Bernoulli and binomial distributions in xellstorm
- Make the cell an input and, on the Distributions step, choose Bernoulli and type p, or Binomial and type n and p.
- For a risk event, keep the impact in a cell of its own (a fixed number or another input, such as a PERT) and multiply in the workbook: the occurrence and the size of the risk then stay separate, and the sensitivity analysis shows which one drives the result.
- With past 0/1 outcomes, “From data” in the side panel fits a Bernoulli; with past counts out of a known n, type n under “Binomial: known number of trials” and refit.
“Import from workbook” converts =IF(RAND() < p, 1, 0) into a Bernoulli input and =BINOM.INV(n, p, RAND()) into a binomial one, and add-in models convert from RiskBernoulli, VoseBernoulli, PsiBernoulli, RiskBinomial, VoseBinomial and PsiBinomial.
Questions
How do I simulate whether a risk occurs in Excel?
Use =IF(RAND() < p, 1, 0), which returns 1 with probability p, and multiply it by the risk’s impact. Over many recalculations the share of 1s approaches p. Use a separate RAND() for each risk, so the risks occur independently.
What is the difference between a Bernoulli and a binomial distribution?
A Bernoulli is one try: the result is 0 or 1. A binomial is n independent tries with the same probability, and the result is how many came out 1, from 0 to n. A Bernoulli is the binomial with n = 1.
How do I generate binomial random numbers in Excel?
Use =BINOM.INV(n, p, RAND()): for a batch of 50 with a 4% defect rate, =BINOM.INV(50, 0.04, RAND()). Each recalculation gives a new whole number from 0 to n; their average approaches n × p = 2.
Binomial or Poisson?
Binomial when there is a fixed number of chances, n, each with the same probability: the count cannot exceed n. Poisson when events can happen any number of times in a period at an average rate. When n is large and p small, the two give nearly the same answer.
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. - Risk register
How much contingency does a risk register need?
Probability × likely impact sums to 291; the simulated mean loss is 359 and the worst 5% average 1,436 (USD thousands). Size contingency from the tail. - Guide
Poisson distribution in Excel: counting events, and when to use negative binomial
The Poisson distribution counts events in a period at a known average rate. POISSON.DIST, random Poisson counts in Excel without an inverse, and the negative binomial for counts that vary more.
xellstorm is a browser-based Monte Carlo simulation tool for Excel models: no add-in, and the workbook never leaves your computer.