Solving Problems With the Binomial Distribution in Excel 2010 and Excel 2013

This is one of the following four articles on the Binomial Distribution in Excel

Overview of the Binomial Distribution in Excel 2010 and Excel 2013

Solving Problems With the Binomial Distribution in Excel 2010 and Excel 2013

Normal Approximation of the Binomial Distribution in Excel 2010 and Excel 2013

Distributions Related to the Binomial Distribution

 

Solving Problems With the

Binomial Distribution in

Excel

 

Problem 1 – Solving With

the Binomial’s PDF in

Excel

Calculate the probability of getting exactly 4 heads in 6 flips of a fair coin.

n = 6

k = 4

p = 0.5

Pr(X=k) = BINOM.DIST(k, n, p, FALSE)

Pr(X=4) = BINOM.DIST(4, 6. 0.5, FALSE) = 0.234

There is a 23.4 percent chance of getting exactly 4 heads in 6 flips of a fair coin.

 

Problem 2 – Solving With the

Binomial’s CDF in Excel

Calculate the probability of getting up to 4 heads in 6 flips of a fair coin.

n = 6

k = 4

p = 0.5

Pr(X≤k) = BINOM.DIST(k, n, p, TRUE)

Pr(X≤4) = BINOM.DIST(4, 6. 0.5, TRUE) = 0.891

There is a 89.1 percent chance of getting up to 4 heads in 6 flips of a fair coin.

 

Problem 3 – Calculating a Range of

Binomial Probabilities in Excel

What is the probability that between 10 and 25 products out of 100 require service is 15 percent of all products require service?

Pr( 10≤X≤25 )

This is equal to the intersection of the following two probabilities:

Pr(X≤25 ) AND Pr(X≥10)

which is

= Pr(X≤25 X≥10)

= Pr(X≤25 ) - Pr(X≤9)

Note that the Excel formula to calculate the binomial distribution’s CDF calculates Pr(X≤k). All cumulative binomial probabilities must therefore be expressed as “up to” (less than or equal to) before they can be converted to Excel.

= BINOM.DIST(25,100,0.15,TRUE) – BINOM.DIST(9,100,0.15,TRUE)

= 0.9970 – 0.0551

= 0.9419

There is a 94.19 percent probability that between 10 and 25 products out of 100 will need service if 15 percent of products need service.

 

Problem 4 – Sampling With and

Without Replacement in Excel

A production line makes batches of 200 items. A random sample of 20 items is taken from every batch and inspected. The entire batch is rejected of 3 or more of the sampled items are found to be defective. The production line is known to have a 10 percent defect rate. If the sampling is done without replacement, what is the probability that a batch will pass the inspection?

The hypergeometric distribution would be the correct distribution to solve this problem because sampling is done without replacement. The binomial distribution can be substituted for the hypergeometric distribution if the population size is at least 10 times as large as the sample size. This is the case here since the population size (batch size) is 200 and the sample size is 20.

A batch passes inspection if 2 or fewer items are found to be defective in the inspection of 20 sampled items. The probability of a batch passing inspection is equal to the probability that up to 2 items in each sample are found defective. The CDF (cumulative distribution function) will be used to calculate this.

One reason to estimate the hypergeometric distribution’s CDF with the binomial distribution’s CDF is that Excel only has a formula to calculate the hypergeometric distribution’s PDf but not its CDF. Calculating the hypergeometric distribution’s CDF in Excel would require calculating the hypergeometric distribution’s PDF at every point from 0 to k and then summing those numbers to obtain the hypergoemetric distribution’s CDF at X=k. These calculations will be performed at the end of this problem’s solution and compared with the binomial distribution’s CDF at X = k.

The hypergoemetric distribution’s CDF will be estimated using the binomial distribution’s CDF as follows:

A batch passes inspection if there are 2 or fewer defective items found in each sample of 20 items. The defect rate is known to be 10 percent.

n = 20

k = 2

p = 0.1

Pr(X≤k) = BINOM.DIST(k, n, p, TRUE)

Pr(X≤2) = BINOM.DIST(2, 20. 0.1, TRUE) = 0.6769

There is a 67.69 percent chance of finding up to 2 defects in each sample of 20 if the defect rate is 10 percent and the sample is done with replacement. The sampling is done without replacement so the exact answer would require the use of the hypergeometric distribution. Since the population size is 10 times as large as the sample size, the hypergeometric distribution can be approximated by using the binomial distribution is as done here.

The exact answer using the hypergeometric distribution will be calculated and then compared to the binomial distribution’s estimate as follows:

Excel only provides a formula to calculate the PDF of the hypergeometric distribution but not the CDF. All versions of Excel calculate the hypergeometric distribution’s PDF with the formula HYPGEOMDIST(). Excel 2010 and later also calculate the hypergeometric distribution’s PDF with the formula HYPGEOM.DIST(). Both produce equivalent results.

The hypergeometric distribution is a discrete distribution just like the binomial. This means that the hypergeometric distribution only has values of its PDF and CDF at integer values of k. The distribution’s CDF is calculated as follows:

Pr(X≤k)

Since the hypergeometric distribution is discrete and values only at integer values of k, the CDF can be calculated by summing the PDF at each integer from 0 to k as follows:

Pr(X≤k) = Pr(X=0) + Pr(X=1) + Pr(X=2) + … + Pr(X=k)

This method will have to be used because Excel only has a formula to calculate the hypergeometric distribution’s PDF but not its CDF.

The Excel formula to calculate the hypergeometric distribution’s PDF is as follows:

Pr(X=k) = HYPGEOM.DIST(k, n, K, N)

k = number of successes in each sample

n = sample size = 20

K = number of successes in the population = 20

= (Known defect rate) * (Population size)

= 0.10 * 200 = 20

N = population size =200

Calculating the hypergeometric distribution’s CDF would be done as follows in Excel (this uses the legacy formula HYPGEOMDIST, which is equivalent to the current formula HYPGEOM.DIST):

(Click On Image To See a Larger Version)

An entire batch passes inspection if 2 or fewer items in the random sample of 20 items are found defective. The probability of a batch passing inspection is therefore Pr(X≤2).

(Click On Image To See a Larger Version)

HYPGEOMDIST(0,20,20,200) = HYPGEOM.DIST(0,20,20,200,FALSE) = 0.1085

HYPGEOMDIST(1,20,20,200) = HYPGEOM.DIST(1,20,20,200,FALSE) = 0.2697

HYPGEOMDIST(2,20,20,200) = HYPGEOM.DIST(2,20,20,200,FALSE) = 0.3004

Pr(X≤2) = 0.6787

There is a 67.87 percent chance of finding up to 2 defects in each sample of 20 taken from a population of 200 if the defect rate is 10 percent and the sample is done without replacement.

The binomial distribution estimate of Pr(X≤2) is 0.6769 which is fairly close to exact answer of 0.6787 found using the hypergeometric distribution.

 

Excel Master Series Blog Directory

 

Click Here To See a List Of All

Statistical Topics And Articles In

This Blog

 

You Will Become an Excel Statistical Master!