MSIS 111 PROBABILITY EXCEL

5-1

Chapter 5: Excel Lab

Lab Instructions: 1. Open Excel File

2. Follow instructions to perform solution for all Problems #5.1 to 5.6 in this Excel Guide.

3. Label Excel Tabs 5.1, 5.2, 5.3, 5.4, 5.5 and 5.6 in Excel

4. Upon completion, submit completed assignment via Blackboard: Include all Group Member

names as they are listed exactly in Blackboard. Nicknames and abbreviated names will result in

significant grade delays.

Discrete Distributions In this chapter, we examine the probabilities of events occurring in experiments that produce such discrete

distributions. In particular, we will study the binomial distribution, the Poisson distribution, and the

hypergeometric distribution.

Discrete Versus Continuous Distributions A random variable is a variable that contains the outcomes of a chance experiment. A random variable is a

discrete random variable if the set of all possible values is at most a finite or a countably infinite number

of possible values

Continuous random variables take on values at every point over a given interval. Thus, continuous

random variables have no gaps or unassumed values.

 The outcomes for random variables and their associated probabilities can be organized into distributions. The two types of distributions are discrete distributions, constructed from discrete

random variables, and continuous distributions, based on continuous random variables.

Describing a Discrete Distribution Mean, Variance, and Standard Deviation of Discrete Distributions

The measures of central tendency and measures of variability can be applied to discrete distributions to

compute a mean, a variance, and a standard deviation. Each of those three descriptive measures (mean,

variance, and standard deviation) is computed on grouped data by using the class midpoint as the value to

represent the data in the class interval. With discrete distributions,

using the class midpoint is not necessary because the discrete value of an outcome (0, 1, 2, 3, . . .) is used

to represent itself. Thus, instead of using the value of the class midpoint (M) in computing these descriptive

measures for grouped data, the discrete experiment’s outcomes (x) are used. In computing these descriptive

measures on grouped data, the frequency of each class interval is used to weight the class midpoint. With

discrete distribution analysis, the probability of each occurrence is used as the weight.

P a g e | 2

Mean or Expected Value

The mean or expected value of a discrete distribution is the long-run average of occurrences. We must

realize that any one trial using a discrete random variable yields only one outcome. However, if the process

is repeated long enough, the average of the outcomes are most likely to approach a long-run average,

expected value, or mean value. This mean, or expected, value is computed as follows.

 ( ) ( )E x x P x   where E(x) = long-run average, x = an outcome, and P (x) = probability of that outcome.

Variance and Standard Deviation of a Discrete Distribution

The variance and standard deviation of a discrete distribution are solved for by using the outcomes (x)

and probabilities of outcomes [P(x)] in a manner similar to that of computing a mean. In addition, the

computations for variance and standard deviations use the mean of the discrete distribution. The formula

for computing the variance follows:

2 2( ( )x P x       where x = an outcome, P(x) = probability of a given outcome,  = mean.

The standard deviation is then computed by taking the square root of the variance.

Demonstration Problem 5.1 1. During one holiday season, the Texas lottery played a game called the Stocking Stuffer. With this

game, total instant winnings of $34.8 million were available in 70 million $1 tickets, with ticket

prizes ranging from $1 to $1,000. Shown here are the various prizes and the probability of winning

each prize. Use these data to compute the expected value of the game, the variance of the game, and

the standard deviation of the game.

Prize (x) Probability P(x) $1,000 .00002

100 .00063

20 .00400

10 .00601

4 .02403

2 .08877

1 .10479

0 .77175

2. Input the data into Excel and set up the x*p(x) with a simple formula copied down. The total calculated by a SUM function.

Chapter 5 5-3

3. The expected payoff for a $1 ticket in this game is 60.2 cents (rounded). A Player could expect to average about 60 cents in winnings. In the long run, the participant will lose about $1.00 - .602 =

.398, or about 40 (rounded) cents a game. Of course, an individual will never win 60 cents in any

one game. Why? We are measuring over the long run…for each specified trial in one experiment!

4. The variance and standard deviation can be calculated by adding the following columns:

5. The variance is 28.98349 (dollars)2 and the standard deviation is $5.38.

P a g e | 4

Binomial Distribution

Several assumptions underlie the use of the binomial distribution: the experiment involves n identical trials;

each trial has only two possible outcomes denoted as success or as failure; each trial is independent of the

previous trials; the terms p and q remain constant throughout the experiment, where the term p is the

probability of getting a success on any one trial and the term q =(1- p) is the probability of getting a failure

on any one trial.

As the word binomial indicates, any single trial of a binomial experiment contains only two possible

outcomes. These two outcomes are labeled success or failure. The probability of success p is the same for

each observation. The binomial formula is shown as:

! ( )

!( )!

x n x x n x

n x

n P x C p q p q

x n x

      

where n = the number of trials (or the number being sampled)

x = the number of successes desired

p = the probability of getting a success in one trial

q = 1 − p = the probability of getting a failure in one trial

Binomial probabilities can be calculated using a function in Excel.

Note: Visit Blackboard, as there are two ways to solve a Binomial Distribution using the written formula.

We may use the factorial version of the combination version. Each variation of the formula is

demonstrated via Blackboard.

Demonstration Problem 5.2 A Gallup survey found that 65% of all financial consumers were very satisfied with their primary financial

institution. Suppose that 25 financial consumers are sampled and if the Gallup survey result still holds true

today, what is the probability that exactly 19 are very satisfied with their primary financial institution?

1. To compute probabilities from a binomial distribution, select BINOM.DIST from the Insert Function’s Statistical menu.

Chapter 5 5-5

2. In the BINOM.DIST dialog box, there are four arguments to this function. On the first line, Number_s, enter the value of x, the number of successes (19 in this case). On the second line, Trials,

enter the number of trials (sample size, n) (25 in this case). On the third line, Probability’s, enter

the value of p (0.65 probability of success).

3. The fourth line, Cumulative, requires a logical response of either TRUE or FALSE. Place TRUE in the slot to get the cumulative probabilities for all values from 0 to x. Place FALSE in the slot to

get the exact probability of getting x successes in n trials (0 for this example).

4. The probability is 0.0908. The function is =BINOM.DIST(19,25,0.65,0)

Demonstration Problem 5.3 Recall early in this section that according to Small Business Trends, 68% of small business clients who

leave do so because they feel unappreciated, unimportant, and taken for granted. Supposed 20 business

clients who have left a small business, are randomly interviewed and asked why they left. What is the

probability that 18 or more left because they felt unappreciated, unimportant, and taken for granted?

1. To compute probabilities from a binomial distribution, select BINOM.DIST from the Insert Function’s Statistical menu.

2. The function arguments are filled in as follows for 18 or more:

P a g e | 6

3. The function arguments are filled in as follows for exactly 19 is calculated the same way and for exactly 20. The resulting probabilities are: 0.188 + 0.0042 + 0.0004 = 0.0234. The individual functions are

shown below:

4. The probability that 18 or more left because they felt unappreciated, unimportant, and taken for granted is 0.0234.

Demonstration Problem 5.4 One study by CNNMoney reported that 60% of workers have less than $25,000 in total savings and

investments (excluding the value of their home). If this is true and if a random sample of 20 workers is

selected, what is the probability that fewer than 10 have less than $25,000 in total savings and investments?

1. To compute probabilities from a binomial distribution, select BINOM.DIST from the Insert Function’s Statistical menu.

2. The function arguments are filled in as follows for fewer than 10 are shown below. The number 9 is input to represent fewer than 10.

3. The probability that fewer than 10 have less than $25,000 is 0.127. The function is =BINOM.DIST(9,20,0.6,1)

Chapter 5 5-7

Demonstration Problem 5.5 A manufacturing company produces 10,000 plastic mugs per week. This company supplies mugs to another

company, which packages the mugs as part of picnic sets. The second company randomly samples 10 mugs

sent from the supplier. If two or fewer of the sampled mugs are defective, the second company accepts the

lot. What is the probability that the lot will be accepted if the mug manufacturing company actually is

producing mugs that are 10% defective? 20% defective? 30% defective? 40% defective?

1. To computer probabilities from a binomial distribution, select BINOM.DIST from the Insert Function’s Statistical menu.

2. The function arguments are filled in as follows for 10% probability:

3. The probability that the lot will be accepted if the mug manufacturing company actually is producing mugs that are 10% defective is 0.930. The function is =BINOM.DIST(2,10,0.1,1).

4. A table can be set up in Excel to calculate probabilities based on the other % defective values. Instead of typing in the probability, click on that cell in the formula. Then you can copy the formula down for

all values.

5. These values indicate that if the manufacturing company is producing 10% defective mugs, the probability is relatively high (0.930) that the lot will be accepted by chance. For higher values of p, the

probability of lot acceptance by chance decreases. In addition, as p increase, the expected value moves

away from the acceptable values, x < 2. This move reduces the chances of lot acceptance.

Using the Computer to Produce a Binomial Distribution

P a g e | 8

Excel can be used to produce the probabilities for virtually any binomial distribution.

Mean and Standard Deviation of a Binomial Distribution

A binomial distribution has an expected value or a long-run average, which is denoted by µ. The value of

µ is determined by pn  . The long-run average or expected value means that, if n items are sampled

over and over for a long time and if p is the probability of getting a success on one trial, the average number

of successes per sample is expected to be pn  .   pn 

n p q  

Examining the mean of a binomial distribution gives an intuitive feeling about the likelihood of a given

outcome.

Demonstration Problem 5.6 A Gallup survey found that 65% of all financial consumers were very satisfied with their primary According

to the U.S. Census Bureau, approximately 6% of all workers in Jackson, Mississippi, are unemployed. In

conducting a random telephone survey in Jackson, what is the probability of getting two or fewer

unemployed workers in a sample of 20?

1. Open an empty worksheet in Excel and type "x" into cell A1, "Prob (x)" into cell B1 and "Prob (< x)" into cell C1.

2. Input “0” into cell A2, 1 into cell A2 and fill down to 9 in cell A11.

3. Input the following function into cell B2: =BINOM.DIST(A2,20,0.06,0) and press Enter. This function calculates the exact binomial probability that x number of employees are found unemployed

based on the total number in the sample (20), the given population parameter of 6% unemployed. The

last argument (set to 0) specifies whether to calculate the exact probability for that number of employees

or a cumulative probability. Copy the function down to cell B11.

4. Input the following function into cell C2: =BINOM.DIST(A2,20,0.06,1) and press Enter. This function is identical to the previous function with the exception of the last argument (now set to 1),

which calculates the cumulative probability. Copy the function down to cell C11.

Chapter 5 5-9

5. The resulting table of individual and cumulative probabilities is shown below:

6. The values in cells B11 to B13 could display scientific notation for a value that is almost zero (10-5, 10-7). You can keep the values as they appear or re-format them to fewer decimals by first selecting

those cells, right-click and select Format Cells from the menu, and on the Number tab, select Number for the format and input 4 for the number Decimal Places.

7. The probability x ≤ 2 when n = 20 and p = .06 is 0.8850.