A to Z of Excel Functions: The BINOMDIST Function

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

The BINOMDIST 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.

You are probably thinking you are seeing double if you read last time’s article.  This function also returns the individual term binomial distribution probability.  The BINOMDIST 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, BINOMDIST can calculate the probability that two of the next three babies born are male.

The BINOMDIST function employs the following syntax to operate:

The BINOMDIST 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 BINOMDIST 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, BINOMDIST returns the #VALUE! error value
• if number_s < 0 or number_s > trials, BINOMDIST returns the #NUM! error value
• if probability_s < 0 or probability_s > 1, BINOMDIST returns the #NUM! error value.

The binomial probability mass function is:

where:

which is COMBIN(n, x).

The cumulative binomial distribution is:

All clear as mud? Please see my example below:

Essentially, BINOMDIST is the slightly less flexible version of BINOM.DIST and represents the version of the function used before its successor was introduced in Excel 2010.  It is still recognised in Excel 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.