Creating a Craps Simulator in Microsoft Excel
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.
Setting Up the Excel Worksheet:
- Set up a worksheet with manual calculation and one iteration. (See Play Bunco with Excel for instructions on how to do this.)
- The worksheet will require several circular references.
- Use the Random Dice example to generate two dice in cells B2 and D3. (See Play Dice Games with Excel for more details.)
- 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 the figure below. (Click any image for a larger view.)
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 the figure below.
3) Finally, in cell D5, enter the circular reference =D18, as shown in the below figure.
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 this Craps Simulator.
Commands Discussed: Tools – Options – Calculation – Manual
Functions Discussed: =IF()
Additional Excel Resources
For more Microsoft Excel tips and tutorials, see the following selections.
91 Tips for Calculating with Microsoft Excel – This collection of Excel tricks, taken from the book Learn Excel from Mr. Excel, covers all facets of Excel calculations from tips for beginners to shortcuts and strategies for more advanced users.
Excel Formatting Tips from Mr. Excel – In addition to showing how to use Excel’s formatting features to make your charts and spreadsheets more visually appealing, this group of 72 tips focuses on ways to use formatting to make it easier to analyze and organize your data.