Did you know you can set up a simulator and play craps in Excel? Follow these simple steps and you can be throwing virtual dice ... and look like you are working on a spreadsheet while doing it.
slide 1 of 2
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 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.
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.