Simulation Classroom Exercises

Exercise 1:
Simulate 100 rolls of an icosahedral (i.e., 20-sided) die. How many of the rolls were within one average deviation of the median?

In cell A1, we want the computer to give us one of the (whole) numbers from 1 to 20, with equal probability. The Excel function
=RANDBETWEEN(1,20)
will do that. [Here is another way to get the same result: The function RAND() takes no "arguments" (i.e., we don't have to give it any values; that's why the parentheses after it are empty) and gives a decimal somewhere between 0 and 1, with "uniform" probability, i.e., any of the numbers is just as likely to come out as any other. To convert to a result between 0 and 20, we multiply by 20; then to round down to the greatest whole number less than or equal to the result -- which will be between 0 and 19 -- we use the TRUNC(...) function; and then we add 1 to get a result between 1 and 20. So we can also use
=TRUNC(20*RAND())+1
to get the result we want.] Then we can highlight cell A1, click on the lower right corner, and drag it down through cells A2 through A100, to get 100 "rolls" of our 20-sided "die".

To answer the question, we need the median and the average deviation (AVEDEV); we can put them in cells B1 and B2 respectively. Then we can put
=B1-B2
in cell B3 and
=B1+B2
in cell B4, and use them as the "bin range" in the FREQUENCY function to give the numbers of the 100 "rolls" that are below, within, or above one average deviation of the median. The FREQUENCY function is tricky to enter, however, and the function wizard doesn't do it correctly. The right way to use it is to highlight three adjacent cells in a column (say C3:C5), type
=FREQUENCY(A1:A100,B3:B4)
and, instead of just hitting the Enter key, hold down the Ctrl and Shift keys while hitting Enter. (If you make just hit Enter -- which is very easy to do, out of habit -- delete the contents and start over: Highlight three ...)

You have probably noticed that the values change every time you put something new into a cell. Excel recomputes the RAND function, i.e., rerolls its internal dice, whenever something is added to the spreadsheet. As a result, if there are a great many cells using that function, entering something new can take a few seconds.

Exercise 2:
Simulate playing "Rock, Paper, Scissors" 30 times and 300 times. In each case, how many of the rounds are not ties?

You know how to play the game: At a signal, both players display one of three options, rock (a fist), paper (an hand stretched flat) or scissors (two fingers); if they display the same thing, the round is a tie (and otherwise "rock breaks scissors, scissors cuts paper, and paper covers rock", but we don't need to know that). We use column A to record what is displayed by player A, column B to record B's choice, and column C to count the non-ties.

In cell A1, we want 0, 1 or 2 (representing rock, paper and scissors, respectively) to appear with equal probabilities, so we can use RANDBETWEEN(0,2). Then we can copy it into column B; and in cell C1 we use the IF function to put in a 1 if the contents of A1 and B1 are not equal and 0 otherwise:
=IF(A1<>B1,1,0)
So IF takes three arguments; the first is a condition, either true or false, the second is the value in the cell if the condition is true, and the third the value if the condition is false. In this case we have used the "greater than or less than" symbols <> to mean "not equal to". Then we can highlight cells A1:C1 and drag their contents down through the rectangle A1:C300.

Finally, because we used 1's for non-ties and 0's for ties, we can get the number of non-ties in the first 30 rounds of the game (rows 1-30) by entering (maybe into cell D1) the command
=SUM(C1:C30)

For the remaining exercises, you are left to figure out how to create the simulation.

Exercise 3:

1. Create 20 randomly generated whole numbers between 1 and 10 (inclusive) so that each digit is equally likely to appear, whether or not it appears elsewhere.
2. Find the average and standard deviation of the numbers.
3. Using the Frequency function, find out how many are within one SD of the average.
4. Check your results by counting by hand.
5. Create a new list of 1000 such numbers and find out how many are within one SD of the average.
6. Does this match what we expect for a normal distribution?

Exercise 4:
Simulate two people picking numbers 1, 2 or 3. If they match, person A wins: if they do not match, person B wins. Determine how many of 100 games person A wins.

Exercise 5:

1. Simulate 50 coins being tossed, and count the number of times a head comes up.
2. Set up 30 of these simulations in a single spreadsheet.
3. Use the Frequency command to determine how many of these 30 simulations have
   • 2 or fewer heads
   • 7 or more heads
   • more than half heads