Summary

The Excel BINOM.DIST function returns the individual term binomial distribution probability. You can use BINOM.DIST to calculate probabilities that an event will occur a certain number of times in a given number of trials.

Purpose 

Get binomial distribution probability

Return value 

Calculated probability

Syntax

=BINOM.DIST(number_s,trials,probability_s,cumulative)
  • number_s - The number of successes.
  • trials - The number of independent trials.
  • probability_s - The probability of success on each trial.
  • cumulative - TRUE = cumulative distribution function, FALSE=probability mass function.

How to use 

The BINOM.DIST function returns the individual term binomial distribution probability. You can use BINOM.DIST to calculate probabilities that an event will occur a certain number of times in a given number of trials. BINOM.DIST returns probability as a decimal number between 0 and 1.

Binary data occurs when an observation can be placed into only two categories. For example, when tossing a coin, the result can only be heads or tails. Or, when rolling a die, the result can either be 6 or not 6.

Example

In the example shown, the BINOM.DIST function is used to calculate the probability of rolling a 6 with a die. Since a die has six sides, the probability of rolling a 6 is 1/6, or 0.1667. Column B holds the number of trials, and the formula in C5, copied down, is:

=BINOM.DIST(B5,10,0.1667,TRUE) // returns 0.1614

which returns the probability of rolling zero 6s in 10 trials, about 16%. The probability of rolling one 6 in 10 trials is about 32%.

The formula in D5 is the same, except the cumulative argument has been set to TRUE. This causes BINOM.DIST to calculate the probability that there are "at most" X successes in a given number of trials. The formula in D5, copied down, is:

=BINOM.DIST(B5,10,0.1667,TRUE) // returns 0.1614

In cell D5, the result is the same as C5 because the probability of rolling at most zero 6s is the same as the probability of rolling zero 6s. In cell D8, the result is 0.9302, which means the probability of rolling at most three 6s in 10 rolls is about 93%.

Notes

  • BINOM.DIST returns probability as a decimal number between 0 and 1.
  • Number_s should be an integer, and will be truncated to an integer if not.
  • Trials should be an integer, and will be truncated to an integer if not.
  • If number_s, trials, or probability_s are not numbers, BINOM.DIST returns a #VALUE! error.
  • If number_s < 0 or number_s > trials, BINOM.DIST returns a #NUM! error.
  • If probability_s < 0 or probability_s > 1, BINOM.DIST returns a #NUM! error value.
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.