In Excel, binomial distributions let you calculate probabilities in two situations. In addition, you should be familiar with the sole hypergeometric distribution function because it is related to binomial functions. You would use binomial distributions in these situations:
When you have a limited number of independent trials, or tests, which can either succeed or fail
When success or failure of any one trial is independent of other trials
BINOM.DIST: Binomial probability distribution
The BINOM.DIST function finds the binomial distribution probability. The function uses the syntax
=BINOM.DIST(number_s,trials,probability_s,cumulative)
where number_s is the specified number of successes that you want, trials equals the number of trials you’ll look at, probability_s equals the probability of success in a trial, and cumulative is a switch that’s set to either the logical value TRUE (if you want to calculate cumulative probability) or the logical value FALSE (if you want to calculate the exact probability).
For example, if a publisher wants to know the probability of publishing three best-selling books out of a set of ten books when the probability of publishing a best-selling book is ten percent, the formula is
=BINOM.DIST(3,10,.1,FALSE)
which returns the value . This indicates that there’s roughly a 6-percent chance that in a set of ten books, a publisher will publish exactly three best-selling books.
To calculate the probability that a publisher will publish either one, two, or three bestsellers in a set of ten books, the formula is
=BINOM.DIST(3,10,.1,TRUE)
which returns the value , which indicates that there is roughly a 99-percent chance that a publisher will publish between one and three bestsellers in a set of ten books.
BINOM.INV: Binomial probability distribution
The BINOM.INV functions find smallest value for which the cumulative binomial distribution equals or exceeds a specified criterion, or alpha, value. The function uses the syntax
=BINOM.INV(trials,probability_s,alpha)
where trials equals the number of Bernoulli trials you’ll look at, probability_s equals the probability of success in a trial, and alpha equals the criterion value you want to meet or beat.
If you set the trials to 10, the probability to .5 and the criterion value to .75, for example, the formula is
=BINOM.INV(10,0.5,0.75)
which returns the value 6.
BINOM.DIST.RANGE: Binomial probability of Trial Result
The BINOM.DIST.RANGE function finds the probability of a trial result or a range of trial results for a binomial distribution. The function uses the syntax
=BINOM.DIST.RANGE(trials,probability_s,number_s,[number_s2])
where trials equals the number of trials you’ll look at, probability_s equals the probability of success in a trial, number_s sets the number of successful trials, and number_s2 (which is an optional argument) sets the maximum number of successful trials.
If you set the trials to 10, the probability to .5 and the number of successful trials to 3, for example, the formula is
=BINOM.DIST.RANGE(10,0.5,3)
which returns the value 0.11718, meaning the probability of having exactly three successful trials equals roughly 12%.
If you set the trials to 10, the probability to .5 and the number of successful trials to anything from 3 to 10, for example, the formula is
=BINOM.DIST.RANGE(10,0.5,3,10)
which returns the value , meaning the probability of the number of successful trials range anywhere from 3 to 10 equals roughly 95%.
NEGBINOM.DIST: Negative binominal distribution
The NEGBINOM.DIST function finds the probability that a specified number of failures will occur before a specified number of successes based on a probability-of-success constant. The function uses the syntax
=NEGBINOM.DIST(number_f,number_s,probability_s)
where number_f is the specified number of failures, number_s is the specified number of successes, probability_s is the probability of success, and cumulative is a switch you set to 0 or FALSE if you want a cumulative distribution and to 1 or TRUE if you want a probability distribution.
For example, suppose you’re a wildcat oil operator and you want to know the chance of failing to find oil in exactly ten wells before you find oil in exactly one well. If the chance for success is 5 percent, you can find the chance that you’ll fail ten times before drilling and finding oil by using the formula
=NEGBINOM.DIST(10,2,.05,0)
which returns the value 0.016465266, indicating that there’s less than a 2-percent chance that you’ll fail ten times before hitting a gusher.
CRITBINOM: Cumulative binomial distribution
The CRITBINOM function, which is really an old Excel function and available in recent versions of Excel for reasons of backwards compatibility, finds the smallest value for which the cumulative binomial distribution equals or exceeds a criterion value. The function uses the syntax
=CRITBINOM(trials,probability_s,alpha)
where trials is the number of Bernoulli trials, probability_s is the probability of success for each trial, and alpha equals your criterion value. Both the probability_s and alpha arguments must fall between 0 and 1.
HYPGEOM.DIST: Hypergeometric distribution
The HYPERGEOMETRIC function returns the probability of a specified number of sample successes. A hypergeometric distribution resembles a binomial distribution except with a subtle difference. In a hypergeometric distribution, the success in one trial affects the success in another trial. Typically, you use the HYPGEOM.DIST function when you take samples from a finite population and don’t replace the samples for subsequent trials. The function uses the syntax
=HYPGEOM.DIST(sample_s,number_sample,population_s,number_pop,cumulative)
where sample_s equals the specified number of sample successes, number_sample gives the size of the sample, population_s gives the number of successes in the population, number_pop gives the size of the population, and cumulative is a switch which tells Excel to return either a cumulative distribution (indicated with a 1 or TRUE argument value) or a probability density (indicated with a 0 or FALSE argument value).
As an example of a hypergeometric distribution, suppose you want to calculate the probability that in a sample of 30 items, 5 will be successful. Further suppose you know that within a 4,000-item population, 1,000 are successful. You use the following formula to make this calculation:
=HYPGEOM.DIST(5,30,1000,4000,0)
which returns the value 0.0104596, indicating that the chances that exactly 5 items will be successful in a set of 30 items given the characteristics of the population equals roughly 10 percent.
