A to Z of Excel Functions: The BINOM.DIST Function

Welcome back to our regular A to Z of Excel Functions blog.  Today we look at the BINOM.DIST function.

The BINOM.DIST function

In probability theory and statistics, the binomial distribution with parameters n and p is the discrete probability distribution of the number of successes in a sequence of n independent success / failure experiments, each of which yields success with probability p.  For the record, a success / failure experiment is also called a Bernoulli experiment or Bernoulli trial.  The binomial distribution is frequently used to model the number of successes in a sample of size n drawn with replacement from a population of size N.

This function returns the individual term binomial distribution probability.  The BINOM.DIST function should be used 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.

The BINOM.DIST function employs the following syntax to operate:

The BINOM.DIST function has the following arguments:

• number_s: this is required and represents the number of successes in trials
• trials: this is also required. This is the number of independent trials
• probability_s: again, required.  This is the probability of success on each trial
• cumulative: also required.  This is a logical value that determines the form of the function.  If cumulative is TRUE, then BINOM.DIST returns the cumulative distribution function, which is the probability that there are at most number_s successes; if cumulative is FALSE, it returns the probability mass function, which is the probability that there are number_s successes.

It should be further noted that:

• number_s and trials are truncated to integers
• if number_s, trials, or probability_s is / are non-numeric, BINOM.DIST returns the #VALUE! error value
• if number_s < 0 or number_s > trials, BINOM.DIST returns the #NUM! error value
• if probability_s < 0 or probability_s > 1, BINOM.DIST returns the #NUM! error value.

The binomial probability mass function is:

where:

which is COMBIN(n, x).

The cumulative binomial distribution is:

Essentially, BINOM.DIST replaces BINOMDIST.  First introduced in Excel 2010, this provides more compatibility with other statistical software.  The former function is still recognised in Excel though for legacy reasons.

All clear as mud? Please see my example below:

Essentially, BINOM.DIST replaces BINOMDIST. First introduced in Excel 2010, this provides more compatibility with other statistical software.  The former function is still recognised in Excel though for legacy reasons.

We’ll continue our A to Z of Excel Functions soon.  Keep checking back – there’s a new blog post every other business day.