1. Gambling

    Chapter 1 The Die Is Cast

    What the simulation does

    We start with a very simple case of simulation—casting 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 sheet—otherwise 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.