Table of Contents
The binomial distribution in Excel can be used to calculate the probability of a certain number of successes in a given number of trials, provided you know the probability of success of a single trial. It is calculated using the BINOM.DIST function, which requires the total number of trials, the number of successes, the probability of success, and a logical value that indicates whether to calculate the cumulative probability or the probability of a single value.
The is one of the most commonly used distributions in statistics. This tutorial explains how to use the following functions in Excel to solve questions about binomial probabilities:
- BINOM.DIST
- BINOM.DIST.RANGE
- BINOM.INV
BINOM.DIST
The function BINOM.DIST finds the probability of getting a certain number of successes in a certain number of trials where the probability of success on each trial is fixed.
The syntax for BINOM.DIST is as follows:
BINOM.DIST(number_s, trials, probability_s_cumulative)
- number_s: number of successes
- trials: total number of trials
- probability_s: probability of success on each trial
- probability_s_cumulative: TRUE returns the cumulative probability; FALSE returns the exact probability
The following examples illustrate how to solve binomial probability questions using BINOM.DIST:
Example 1
Nathan makes 60% of his free-throw attempts. If he shoots 12 free throws, what is the probability that he makes exactly 10?
To answer this question, we can use the following formula in Excel: BINOM.DIST(10, 12, 0.6, FALSE)
The probability that Nathan makes exactly 10 free throw attempts out of 12 is 0.063852.
Example 2
Marty flips a fair coin 5 times. What is the probability that the coin lands on heads 2 times or fewer?
To answer this question, we can use the following formula in Excel: BINOM.DIST(2, 5, 0.5, TRUE)
Example 3
Mike flips a fair coin 5 times. What is the probability that the coin lands on heads more than 3 times?
To answer this question, we can use the following formula in Excel: 1 – BINOM.DIST(3, 5, 0.5, TRUE)
The probability that the coin lands on heads more than 3 times is 0.1875.
Note: In this example, BINOM.DIST(3, 5, 0.5, TRUE) returns the probability that the coin lands on heads 3 times or fewer. So, to find the probability that the coin lands on heads more than 3 times, we simply use 1 – BINOM.DIST(3, 5, 0.5, TRUE).
BINOM.DIST.RANGE
The function BINOM.DIST.RANGE finds the probability of getting a certain number of successes in a certain range, based on a certain number of trials where the probability of success on each trial is fixed.
The syntax for BINOM.DIST.RANGE is as follows:
BINOM.DIST.RANGE(trials, probability_s, number_s, number_s2)
- trials: total number of trials
- probability_s: probability of success on each trial
- number_s: minimum number of successes
- number_s2: maximum number of successes
The following examples illustrate how to solve binomial probability questions using BINOM.DIST.RANGE:
EXAMPLE 1
Debra flips a fair coin 5 times. What is the probability that the coin lands on heads between 2 and 4 times?
To answer this question, we can use the following formula in Excel: BINOM.DIST.RANGE(5, 0.5, 2, 4)
The probability that the coin lands on heads between 2 and 4 times is 0.78125.
EXAMPLE 2
It is known that 70% of men support a certain law. If 10 men are randomly selected, what is the probability that between 4 and 6 of them support the law?
To answer this question, we can use the following formula in Excel: BINOM.DIST.RANGE(10, 0.7, 4, 6)
The probability that between 4 and 6 of the randomly selected men support the law is 0.339797.
EXAMPLE 3
Teri makes 90% of her free-throw attempts. If she shoots 30 free throws, what is the probability that she makes between 15 and 25?
To answer this question, we can use the following formula in Excel: BINOM.DIST.RANGE(30, .9, 15, 25)
The probability that she makes between 15 and 25 free throws is 0.175495.
BINOM.INV
The function BINOM.INV finds the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value.
The syntax for BINOM.INV is as follows:
BINOM.INV(trials, probability_s, alpha)
- trials: total number of trials
- probability_s: probability of success on each trial
- alpha: criterion value between 0 and 1
The following examples illustrate how to solve binomial probability questions using BINOM.INV:
EXAMPLE 1
Duane flips a fair coin 10 times. What is the smallest number of times the coin could land on heads so that the cumulative binomial distribution is greater than or equal to 0.4?
To answer this question, we can use the following formula in Excel: BINOM.INV(10, 0.5, 0.4)
The smallest number of times the coin could land on heads so that the cumulative binomial distribution is greater than or equal to 0.4 is 5.
EXAMPLE 2
Duane flips a fair coin 20 times. What is the smallest number of times the coin could land on heads so that the cumulative binomial distribution is greater than or equal to 0.4?
To answer this question, we can use the following formula in Excel: BINOM.INV(20, 0.5, 0.4)
The smallest number of times the coin could land on heads so that the cumulative binomial distribution is greater than or equal to 0.4 is 9.
EXAMPLE 3
Duane flips a fair coin 30 times. What is the smallest number of times the coin could land on tails so that the cumulative binomial distribution is greater than or equal to 0.7?
To answer this question, we can use the following formula in Excel: BINOM.INV(20, 0.5, 0.4)
The smallest number of times the coin could land on tails so that the cumulative binomial distribution is greater than or equal to 0.7 is 16.