Definition 1: Suppose an experiment has the following characteristics:
- the experiment consists of n independent trials, each with two mutually exclusive outcomes (success and failure)
- for each trial the probability of success is p (and so the probability of failure is 1 – p)
Each such trial is called a Bernoulli trial. Let x be the discrete random variable whose value is the number of successes in n trials. Then the probability distribution function for x is called the binomial distribution, B(n, p), and is defined as follows:
where C(n, x) = and n! = n(n–1)(n–2)⋯3∙2∙1 as described in Combinatorial Functions.
C(n, x) can be calculated by using the Excel function COMBIN(n,x). See Figure 2 of Built-in Excel Functions for more details about this function.
Observation: Figure 1 shows a graph of the probability density function for B(20, .25).
Figure 1 Binomial distribution
That the graph looks a lot like the normal distribution is not a coincidence (see Relationship between Binomial and Normal Distributions)
Click here for a proof of Property 1.
Excel Function: Excel provides the following functions regarding the binomial distribution:
BINOMDIST(x, n, p, cum) where n = the number of trials, p = the probability of success for each trial and cum takes the value TRUE or FALSE.
BINOMDIST(x, n, p, FALSE) = probability density function f(x) value at x for the binomial distribution B(n, p), i.e. the probability that there are x successes in n trials where the probability of success on any trial is p.
BINOMDIST(x, n, p, TRUE) = cumulative probability distribution F(x) value at x for the binomial distribution B(n, p), i.e. the probability that there are at most x successes in n trials where the probability of success on any trial is p.
CRITBINOM(n, p, 1 – α) = critical value; i.e. the minimum value of x such that F(x) ≥ 1 – α, where F is the cumulative binomial distribution function for B(n, p)
Excel 2010/2013 provide the following additional functions: BINOM.DIST, which is equivalent to BINOMDIST, and BINOM.INV, which is equivalent to CRITBINOM.
Excel 2013 introduces the following new function (where x ≤ y ≤ n):
BINOM.DIST.RANGE(n, p, x, y) = the probability there are between x and y successes (inclusive) in n trials where the probability of success on any trial is p
Thus, BINOM.DIST.RANGE(n, p, x, y) = BINOMDIST(y, n, p, TRUE) – BINOMDIST(x–1, n, p, TRUE) if x > 0 and BINOM.DIST.RANGE(n, p, 0, y) = BINOMDIST(y, n, p, TRUE). The y parameter may be omitted, in which case BINOM.DIST.RANGE(n, p, x) = BINOMDIST(x, n, p, FALSE).
Example 1: What is the probability that if you throw a die 10 times it will come up six 4 times?
We can model this problem using the binomial distribution B(10, 1/6) as follows
Alternatively the problem can be solved using the Excel function:
BINOMDIST(4, 10, 1/6, FALSE) = 0.054266