A Guide to Using the Binomial Distribution in Excel

Excel Guide

The binomial distribution 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)

Binomial distribution in Excel

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)

Binomial distribution with coin flips in Excel

The probability that the coin lands on heads 2 times or fewer is 0.5.

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)

Cumulative binomial distribution in Excel

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)

Binomial distribution in Excel example

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)

Binomial distribution in Excel

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)

Binomial distribution in Excel

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)

BINOM.INV example in Excel

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)

Binom.INV example in EXCEL

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)

BINOM.INV example in Excel

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.

Additional Resources

An Introduction to The Binomial Distribution
Binomial Distribution Calculator

Leave a Reply

Your email address will not be published. Required fields are marked *