dp.cx blog

A friend was trying to perform some probability calculations for a dice-based game they're playing and couldn't get Excel to give back the values they wanted. So they asked me for help.

The Excel function BINOM.DIST documentation isn't super clear to those that aren't statisticians:

Returns the individual term binomial distribution probability. Use BINOM.DIST in problems with a fixed number of tests or trials, when the outcomes of any trial are only success or failure, when trials are independent, and when the probability of success is constant throughout the experiment. For example, BINOM.DIST can calculate the probability that two of the next three babies born are male.

Essentially, if you want to know the chances of throwing a die and having its value be above 4, you can easily perform that calculation with BINOM.DIST:

=BINOM.DIST(1, 1, (7-5)/6, FALSE)

That last value is a bit tricky, though. If the value is FALSE, it will tell you the probability that there is exactly 1 result (which in the case of throwing one die, is fine). If the value is TRUE, it will tell you the probability that there is at most 1 result (which again, in the case of throwing one die, is fine).

But when you start throwing multiple dice, you often want to know that there are at least some number of results. In that case, what you probably want is BINOM.DIST.RANGE, which has a slightly different set of parameters:

=BINOM.DIST($trials, $probability, $minimum_successes, $maximum_successes)

This way, you can easily find out the chances of having at least 1 die with a value above 4 when throwing 5 die, you'll see the value come out as 96.88%, which is the expected result.