Chapter 56: The Binomial, Hypergeometric, and Negative Binomial Random Variables | MicrosoftВ® Office ExcelВ® 2007: Data Analysis and Business Modeling (Bpg -- Other)

Overview

What is a binomial random variable?
How do I use the BINOMDIST function to compute binomial probabilities?
If equal numbers of people prefer Coke to Pepsi and Pepsi to Coke and I ask 100 people whether they prefer Coke to Pepsi, what is the probability that exactly 60 people prefer Coke to Pepsi and the probability that between 40 and 60 people prefer Coke to Pepsi?
Of all the elevator rails my company produces, 3 percent are considered defective. We are about to ship a batch of 10,000 elevator rails to a customer. To determine whether the batch is acceptable, the customer will randomly choose a sample of 100 rails and check whether each sampled rail is defective. If two or fewer sampled rails are defective, the customer will accept the batch. How can I determine the probability that the batch will be accepted?
Airlines do not like flights with empty seats. Suppose that, on average, 95 percent of all ticket purchasers show up for a flight. If the airline sells 105 tickets for a 100-seat flight, what is the probability that the flight will be overbooked?
What is the hypergeometric random variable?
What is the negative binomial random variable?

What is a binomial random variable?
A binomial random variable is a discrete random variable used to calculate probabilities in a situation where all three of the following apply:
- n independent trials occur.
- Each trial results in one of two outcomes: success or failure.
- In each trial, the probability of success (p) remains constant.
In such a situation, the binomial random variable can be used to calculate probabilities related to the number of successes in a given number of trials. We let x be the random variable denoting the number of successes occurring in n independent trials when the probability of success on each trial is p. Here are some examples in which the binomial random variable is relevant.
Coke or Pepsi Assume that equal numbers of people prefer Coke to Pepsi and Pepsi to Coke. We ask 100 people whether they prefer Coke to Pepsi. We’re interested in the probability that exactly 60 people prefer Coke to Pepsi and the probability that from 40 through 60 people prefer Coke to Pepsi. In this situation, we have a binomial random variable defined by the following:
- Trial: survey individuals
- Success: prefer Coke
- p equals 0.50
- n equals 100
Let x equal the number of people sampled who prefer Coke. We want to determine the probability that x=60 and also the probability that 40≤x≤60.
Elevator Rails Of all the elevator rails we produce produce, 3 percent are considered defective. We are about to ship a batch of 10,000 elevator rails to a customer. To determine whether the batch is acceptable, the customer will randomly choose a sample of 100 rails and check whether each sampled rail is defective. If two or fewer sampled rails are defective, the customer will accept the batch. We want to determine the probability that the batch will be accepted.
We have a binomial random variable defined by the following:
- Trial: look at a sampled rail
- Success: rail is defective
- p equals 0.03
- n equals 100
Let x equal the number of defective rails in the sample. We want to find the probability that x≤22
Airline Overbooking Airlines don’t like flights with empty seats. Suppose that, on average, 95 percent of all ticket purchasers show up for a flight. If the airline sells 105 tickets for a 100-seat flight, what is the probability that the flight will be overbooked?
We have a binomial random variable defined by the following:
- Trial: individual ticket holders
- Success: ticket holder shows up
- p equals 0.95
- n equals 105
Let x equal the number of ticket holders who show up. Then we want to find the probability that x≥101.
How do I use the BINOMDIST function to compute binomial probabilities?
Microsoft Office Excel 2007 includes the BINOMDIST function which you can use to compute binomial probabilities. If you want to compute the probability of x or fewer successes for a binomial random variable having n trials with probability of success p, simply enter BINOMDIST(x,n, p,1). If you want to compute the probability of exactly x successes for a binomial random variable having n trials with probability of success of p, enter BINOMDIST(x,n,p,0). Entering 1 as the last argument of BINOMDIST yields a “cumulative” probability; entering 0 yields the “probability mass function” for any particular value. Let’s use the BINOMDIST function to calculate some probabilities of interest. Our work is in the file Binomialexamples.xlsx, which is shown in Figure 56-1.

Figure 56-1: Using the binomial random variable
If equal numbers of people prefer Coke to Pepsi and Pepsi to Coke and I ask 100 people whether they prefer Coke to Pepsi, what is the probability that exactly 60 people prefer Coke to Pepsi and the probability that between 40 and 60 people prefer Coke to Pepsi?
We have n=100 and p=0.5. We seek the probability that x=60 and the probability that 40≤x≤60 where x equals the number of people who prefer Coke to Pepsi. First, we find the probability that x=60 by entering the formula BINOMDIST(60,100,0.5,0). Excel returns the value 0.011.
To use the BINOMDIST function to compute the probability that 40≤x≤60, we note that the probability that 40≤x≤60 equals (the probability that x≤60)–(the probability that x≤39). Thus, we can obtain the probability that from 40 through 60 people prefer Coke by entering the formula BINOMDIST(60,100,0.5,1)–BINOMDIST(39,100,0.5,1). Excel returns the value 0.9648. So, if Coke and Pepsi are equally preferred, it is very unlikely that in a sample of 100 people, Coke or Pepsi would be more than 10 percent ahead. If a sample of 100 people shows Coke or Pepsi to be more than 10 percent ahead, we would probably doubt that Coke and Pepsi are equally preferred.
Of all the elevator rails my company produces, 3 percent are considered defective. We are about to ship a batch of 10,000 elevator rails to a customer. To determine whether the batch is acceptable, the customer will randomly choose a sample of 100 rails and check whether each sampled rail is defective. If two or fewer sampled rails are defective, the customer will accept the batch. How can I determine the probability that the batch will be accepted?
If we let x equal the number of defective rails in a batch, we have a binomial random variable with n=100 and p=0.03. We seek the probability that x≤2. We simply enter the formula BINOMDIST(2,100,0.03,1). Excel returns the value 0.42 Thus, the batch will be accepted 42 percent of the time.
Really, our chance of success is not exactly 3 percent on each trial. For example, if the first 10 rails are defective, the chance the next rail is defective has dropped to 290/9990; if the first 10 rails are not defective, the chance the next rail is defective is 300/9990. Therefore, the probability of success on the eleventh trial is not independent of the probability of success on one of the first 10 trials. Despite this fact, the binomial random variable is used as an approximation when a sample is drawn and the sample size is less than 10 percent of the total population. Here, our population size equals 10,000 and our sample size is 100. Exact probabilities involving sampling from a finite population can be calculated with the hypergeometric random variable, which I’ll discuss later in this chapter.
Airlines do not like flights with empty seats. Suppose that, on average, 95 percent of all ticket purchasers show up for a flight. If the airline sells 105 tickets for a 100-seat flight, what is the probability that the flight will be overbooked?
Let x equal the number of ticket holders who show up for the flight. We have n=105 and p=0.95. We seek the probability that x≥101. We note that the probability that x≥101=1–the probability that x≤100. So, to compute the probability that the flight is overbooked, we enter the formula 1–BINOMDIST(100,105,0.95,1). Excel yields 0.392, which means there is a 39.2 percent chance that the flight will be overbooked.
What is the hypergeometric random variable?
The hypergeometric random variable governs a situation such as the following:
- An urn contains N balls.
- Each ball is one of two types (called success or failure).
- There are s successes in the urn.
- A sample of size n is drawn from the urn.
Let’s look at an example in the file Hypergeo.xlsx, which is shown in Figure 56-2. The Excel formula HYPERGEOMDIST(x,n,s,N) gives the probability of x successes if n balls are drawn from an urn containing N balls, of which s are marked as “success.”

Figure 56-2: Using the hypergeometric random variable
For example, suppose that 40 of the Fortune 500 companies have a woman CEO. Then the 500 CEOs are analagous to the balls in the urn (N=500) and the 40 women are representative of the s successes in the urn. Then, copying from D8 to D9:D18 the formula =HYPERGEOMDIST(C8,Sample_size,Population_women,Population_size) gives the probability that a sample of 10 Fortune 500 companies will have 0, 1, 2,…, 10 women CEOs. Here Sample_size=10, Population_women=40, Population_size=500.
We consider finding a woman CEO a success. In our sample of 10, for example, we find a probability of 0.431 that no women CEOs will be in the sample. By the way, we could have approximated this probability with the formula BINOMDIST(0,10,0.08,0), yielding 0.434, which is very close to the true probability of 0.431.
What is the negative binomial random variable?
The negative binomial random variable applies to the same situation as the binomial random variable but the negative binomial random variable gives the probability of f failures occurring before the s’th success. Thus =NEGBINOMDIST(f,s,p) gives the probability that exactly f failures will occur before the s’th success when the probability of success is p for each trial. For example, consider a baseball team that wins 40 percent of their games (see file Negbin.xlsx and Figure 56-3 on the next page). Copying from E9 to E34 the formula =NEGBINOMDIST(D9,2,.4) gives the probability of 0, 1, 2,…, 25 losses occurring before the second win. Note here that success equals the game won. For example, there is a 19.2 percent chance the team will lose exactly one game before winning two games.

Figure 56-3: Using the negative binomial random variable