Chapter 15: Chances, Probability, and Odds: Measuring Uncertainty Excel Project

Download all Excel spreadsheets for this chapter.

In many ways, spreadsheets can be thought of as powerful calculators. Suppose for example, we wish to 'design' a calculating machine that will let the user input two numbers n and r in two different cells and will output the number _nP_r in another cell. Figure 15.1 shows how we could implement such a process on n=7 and r=3 using the factorial command FACT of a spreadsheet.

Figure 15.1: Calculating ₇P₃ on a spreadsheet.

As another example, suppose a fair coin will be tossed several times and that we would like to design a spreadsheet that will allow a user to input the number of times r they would like heads to show up and the number of times s they would like tails to show up (in any order) in two cells and will output the probability of that event occuring in another cell.

We will illustrate the calculation using r=1 and s=4. That is, we want to compute the probability of tossing a coin 5 times and getting exactly one head to show up and exactly 4 heads to show up. Of course, it is clear that the head could occur on any of the 5 tosses so the number of ways in which the event could occur is 5. In general, this is found by computing _(r+s)C_r. Our next step is to calculate the total number of possible outcomes. With 5 tosses, this number is 2⁵. In general, this number will be 2^(r+s). Figure 15.2 shows how this process is implemented on a spreadsheet. The formula entered into the spreadsheet in cell D3 was

=FACT(A3+B3)/(FACT(B3)*FACT(A3)).

Figure 15.2: Calculating the probability of getting 1 head and 4 tails in 5 coin tosses.

To take this one step further and to check that our answer is reasonable, we expand this spreadsheet to consider all possible cases with tossing a coin 5 times. After copying formulas down columns D and E, we sum up the probabilities computed in column E to check that they sum to 1. Indeed they do as Figure 15.3 shows. Also note a row in Pascal's triangle which occurs in column D. This, of course, is not suprising.

Figure 15.3: Checking the probability of getting 1 head and 4 tails in 5 coin tosses.

Suppose that the outcomes of an experiment are numbers (values) called v₁, v₂,...,v_n, and the events of the outcomes have probabilities p₁, p₂,...,p_n respectively. The expected value, EV, of the experiment is the sum of the products

As an example, the expected value of a roll of a six-sided die is

We will design a spreadsheet that will allow the user to input two columns and will output an associated expected value. Column A will consist of values of the outcomes of an experiment and column B will consist of the probabilities of each of those events occurring. Then, we will use the design to compute the expected value of a fair, but strange, six-sided die that has values {2,3,4,5,6,7}.

We start by entering in the data for the six-sided die of interest. Notice in Figure 15.4 how we can use formulas to ease our pain of data entry. The expected value of the die is then calculated in cell B8 using the SUM command. An AVERAGE command in the spreadsheet would also work in this particular example.

Figure 15.4: Computing the expected value of a strange six-sided die.

Walking

Exercise 15.1 Design a spreadsheet that will allow a user to input two numbers n and r in two different cells and will output the number _nC_r in another cell.

Exercise 15.2 Design a spreadsheet that will allow a user to input a probability p of an event A occuring in one cell and will output a corresponding chance that event A will occur as a percentage in another cell.

Exercise 15.3 Design a spreadsheet that will allow a user to input a probability p of an event A occuring in one cell and will output the probability that the event A will not occur in another cell.

Exercise 15.4 Design a spreadsheet that will allow a user to input m and n representing the odds in favor of an event E (m to n) and outputs the probability of event E occuring to a third cell. Test your design on the odds in favor of rolling a total of 7 when rolling two die. The odds in favor are 6 to 30. The probability is, of course, 1/36.

Exercise 15.5 Design a spreadsheet that will allow a user to determine how many outfits they can make if in one cell the user inputs the number s of shoes they have, in another cell the user inputs the number t of trousers they have, and in a third cell the user inputs the number u of ugly shirts they have. Put the output in a fourth cell of your choice.

Exercise 15.6 Design a spreadsheet that will allow a user to determine how many unique outcomes (so that order matters!) are possible in tossing a fair coin n times. Let the user input n and output the result in another cell of your choice.

Exercise 15.7 Design a spreadsheet that will allow a user to determine how many unique outcomes (so that order matters!) are possible in tossing a fair six-sided die n times. Let the user input n and output the result in another cell of your choice.

Exercise 15.8 Design a spreadsheet that will allow a user to determine the probability that they will make n free throws in a row. Let the user input the probability p that they can make any given free throw and the number n of free throws that they wish to make.

(a) Design a spreadsheet that will allow a user to input a number n of letters in a code word and will output the number of code words possible.
(b) Design a spreadsheet that will allow a user to input a number n of letters in a code word and will output the number of code words possible that have no repeated letters.
(c) How will your designs above change if one digit numbers are also allowed as parts of code words?

The conditional probability of an event A occuring given that an event B has already occurred, denoted Pr(A|B), is given by the formula

as long, of course, as . Here Pr(A and B) refers to the probability of events A and B occurring.

Exercise 15.10 Design a spreadsheet that will allow the user to input the probabilities Pr(A and B) and Pr(B) and will output to a third cell the probability of event A occurring given that event B has occurred.

Exercise 15.11 Design a spreadsheet that will allow the user to input the probabilities Pr(A|B) and Pr(B) and will output to a third cell the probability that event A and event B will occur.

Jogging

Exercise 15.12 Ziggy flips a fair coin 40 times and it lands heads 27 times. Design a spreadsheet that will output to the user how many more times the coin is likely to land heads if the coin is tossed n more times where n is input by the user.

Exercise 15.13 Ziggy flips a fair coin q times and it lands heads p times. Design a spreadsheet that will output to the user how many more times the coin is likely to land heads if the coin is tossed n more times where n, p, and q are input by the user.

Exercise 15.14 If a fair coin is tossed 2N times, use a spreadsheet to compute the probability that an even number of heads will occur in the cases where N=3,4, and 5. Use this information to make a hypothesis about the probability that an even number of heads will occur when a fair coin is tossed 2N times. Does your answer seem reasonable?

Exercise 15.15 Compute the expected value of a fair six-sided die that has values {3,4,5,6,7,8}. Modify the same spreadsheet to compute the expected value of a fair six-sided die that has values {4,5,6,7,8,9}. Then, look for any patterns that will allow you to hypothesize about the expected value of a fair six-sided die that has values {k,k+1,k+2,k+3,k+4,k+5} where k is any positive integer.

Exercise 15.16 Use a spreadsheet to hypothesize about the expected value of a fair six-sided die that has values {2k+1, 2(k+1)+1, 2(k+2)+1, 2(k+3)+1, 2(k+4)+1, 2(k+5)+1} where k is any positive integer. For example, if k=4, the six-sided die has values {9,11,13,15,17,19}.

Exercise 15.17 This exercise requires the reader to be familiar with Chapter 9. Consider the spiraling Fibonacci rectangles as shown in Figure 15.5. At stage N of the process of "growth", an square is attached to the existing rectangle to form a new, larger rectangle nearly similar to the first. Using a spreadsheet, compute the approximate probability that if you pick a point in the M^th rectangle (M large), it will actually be in the square that was attached to form that rectangle.

Figure 15.5: Spiraling Fibonacci Rectangles.

Running

Exercise 15.18 Recall that Pascal's triangle is closely connected to _nC_r. The n^th row in Pascal's triangle are the values of _nC_r for r=0,1,2,...,n. For example, the third row in Pascal's triangle has elements ₃C₀, ₃C₁, ₃C₂, ₃C₃. Using a spreadsheet, generate 7 rows of Pascal's triangle as shown in Figure 15.6. Of course, to make the job less tedious, you will want to copy formulas!

Figure 15.6: Seven rows of Pascal's triangle.

Exercise 15.19 Suppose a regular six-sided die is to be rolled n times. Design a spreadsheet that will allow a user to input the number of times n they would like to roll the die and the number of times r they would like a '1' to show up in two cells and will output the probability of that event occuring in another cell. Hint: Figure 15.7 shows a spreadsheet used in computing the probability of a '1' showing up r times in tossing a die 4 times. Values of r=0,1,...,4 and their associated probabilities are shown. Notice that it is useful to calculate _nC_r for each r and also the sum of the calculated probabilities.

Figure 15.7: Computing the probability of a '1' showing up r times in tossing a die 4 times.

© 1995-2001 by Prentice-Hall, Inc. A Pearson Company Legal Notice