See all Microsoft Excel
Strategy: Craps is a game played with two dice. On your first roll, if you roll a 7 or 11, you win. If you roll a 2, 3, or 12, you lose. If you roll a 4, 5, 6, 8, 9, or 10, then this number becomes your point. On subsequent rolls, you must roll your point number again before you roll a 7. If you roll your point, then you win. If you roll anything else, then you lose.
Set up a worksheet with manual calculation and one iteration. The worksheet will require several circular references. Use the Random Dice example to generate two dice in cells B2 and D3.
Use a series of IF formulas to keep track if you win or lose.
1) In columns B and C, enter the text values shown in Fig. 313.
2) In cell D5, enter the value TRUE until the rest of the formulas are set up. In the rest of column D, enter the formulas as shown in Fig. 314.
3) Finally, in cell D5, enter the circular reference =D18, as shown in Fig. 315.
Cell D5 is a circular reference that keeps track of whether this is the first roll in each game. If the value in D5 is True, then the rules in D10 and D11 will determine if you win or lose on the first roll of the game. The circular reference in D6 will keep track of the roll number. Once this cell is above 1, then the rules in D15 and D16 will keep watch to see if you roll a 7 or your point.
Rows 18 and 19 keep track if this dice roll ended the current game and the result of that game. Two circular formulas in D21 and D22 will keep track of your lifetime wins and losses in the game. To play, press the F9 key to roll the dice.
Summary: Using intentional circular references, you can design simple games like Craps.
Commands Discussed: Tools – Options – Calculation – Manual
Functions Discussed: =IF()
See all Microsoft Excel