Verschuuren - 100 Excel Simulations

1. Gambling

   Chapter 1 The Die Is Cast

   What the simulation does

   We start with a very simple case of simulationcasting a die (on sheet Dice in 1-Gambling.xlsx). In cell A1 is a formula that generates a random number between 1 and 6. According to that outcome, the colored die shows the appropriate number of eyes at their proper locations. Each time the random number changes, the die adjusts accordingly.

   

   What you need to know

   Cell A1 has a formula that uses a volatile function called RAND. On each recalculation, this function generates a new random number between 0 and 1. Because we want numbers between 1 and 6, we need to multiply by 6, round the number down by using the INT function, and then add 1 to the end result. More in general: =INT((high-low+1)*RAND()+low).

   Users of Excel 2007 and later can also use the easier function RANDBETWEEN which has two arguments for the lower limit (in this case 1) and the upper limit (in this case 6). I decided not to use that function, because in pre-2007 Excel versions this function was only available through the Analysis Toolpak .

   To generate a new random number, you either hit the key F9 or the combination of the Shift key and the F9 key. In this file, I would recommend the latter option ( Shift F9 ), since that would only recalculate the current sheetotherwise you would recalculate all sheets in this file, which may take lots of calculating time.

   Finally, we need to regulate which eyes should pop up for each new random number. This is done inside some of the die cells by using the IF function. This function is a decision maker, which determines whether a specific eye should be on or off.

   What you need to do

1. Type in cell A1: =INT(RAND()*6) + 1. In this case, the function RAND is nested inside the function INT (INT eliminates decimals). Nested functions are very common in Excel; for more information, see Appendix 2.
2. Type in B3: =IF(A1>1,0,). The two double quotes in the last argument return an empty string, showing up as nothing.
3. Type in D3: =IF(A1>3,0,).
4. Type in B5: =IF(A1=6,0,).
5. Type in D5: =IF(A1=6,0,).
6. Type in B7: =IF(A1>3,0,).
7. Type in D7: =IF(A1>1,0,).
8. Type in C5: =IF(OR(A1=1,A1=3,A1=5),0,). In this case, the function OR is nested inside IF. The function OR returns true if any of the enclosed arguments is true.
9. If you want to see all formulas at once, hit Ctr ~ (the tilde can be found below the Esc key). This shortcut toggles the sheet, back and forth, between value-view and formula-view.

Chapter 2 Casting Six Dice

What the simulation does

Open file 1-Gambling.xlsx on sheet 6-Dices. This time we have six different dice. Each die listens to a random number above it, to its left.

The settings for each die are similar to what we did in Simulation 1. The number of eyes for each die is plotted in a column chart below the dice.

A die that shows six eyes gets marked with a color. When there are at least 2 dice in a row with six eyes, all dice get marked at the same time.

What you need to know

There is not much new on this sheet. The main difference is that we need 6 different cells with a RAND function in order to control the six die displays. Each die has the same structure as the one used in Simulation 1.

In addition, we use conditional formatting to change colors of the dice when they show six eyes, or contain at least two dice with six eyes.

What you need to do

1. Make sure all six dice are set up as was done in Simulation 1, but each die is connected to the random cell just above it.
2. Select range A1:C7 first, then Home | Conditional Format | Formula: =$A$1=6.
3. Do something similar for the other five dice.
4. Finally select A3:W7 (that is all six dice) and format them conditionally:
   =COUNTIF($A$1:$U$1,6)>=2.
5. By using ( Sh ) F9 , you may hit a situation like below where at least two dice have six eyes ( F9 recalculates all the sheets of the entire file, whereas Sh F9 only does so for the current sheet and may take less time).

Chapter 3 Frequencies

What the simulation does

Open file 1-Gambling.xlsx on sheet Frequencies. This time we cast two dice at once and sum the number of eyes in column C; we repeat this process 9 more times. In column F, we calculate how often we had a hit of 2 eyes in total, 3 eyes, and so on, up to 12 eyes.

The frequencies are plotted in a graph. Cell F14 calculates the average of column C. It turns color for extreme values. The average is also plotted in the graph as a vertical linebased on the two sets of coordinates shown in E16:F17.

The curve keeps changing each time we hit Shift F9 . Very rarely does it come close to a normal distribution with a mean somewhere in the center. The chance for this to happen would increase if we would have used more dice and more repeats.

What you need to know

On order to calculate frequencies, we need the function FREQUENCY. This is a so-called array function (more on this in Simulations 62 and 63). Such functions return an array or require an array for intermediate calculations. All array functions have to be implemented with three keys at the same time: Ctr Sh Enter .

The function FREQUENCY returns an array of multiple answers based on a set of bins. In this case, the bins are in column E. The function reads the bins as follows: 2 covers all cases up to and including 2, 3 covers all cases >2 and <=3, etc. To make this function work, you need to select all the cells that are going to hold the frequency values all at once, before you use the array function. Once the array function has been implemented with Ctr Sh Enter , you will see the formula in the formula bar surrounded by braceslike this: {=FREQUENCY(,)}. Do not type the braces; they come automatically with Ctr Sh Enter .