Imagine a game when you are bored. You have three dice and you roll them six times. If any die matches the round number you score a point; if all three dice are the same you score 5 points; if all three dice match the round number you score 10 points (or however many you choose).
To create this in Excel you will need three cells set to function as dice. Enter a formula that produces an integer value in the range 1-6.
Enter the following labels into cells A5, A6 and A7: Round, Score, Total Score.
Choose Tools/Options and select the Calculation tab. Untick Recalculate before save. Set Calculation to Manual, turn on Iteration (green tick in the box) and set both the values for Maximum iterations and Maximum change to 1. These settings allow circular cell references because manual calculation only happens once. Manual calculation is performed with the F9 key.
In B5 enter 0.
In B6 enter the following formula:
=IF(AND(B2=D2,D2=F2,B2=B5),15,IF(AND(B2=D2,D2=F2),5,IF(B2=B5,1,0)+IF(D2=B5,1,0)+IF(F2=B5,1,0)))
This is rather long and is best explained in three parts. The first part checks to see if the dice scores are the same and also match the round number, in which case 15 points are scored:
=IF(AND(B2=D2,D2=F2,B2=B5),15,
The second part is for situations where the three dice are the same but do not match the round number, in which case 5 points are scored:
IF(AND(B2=D2,D2=F2),5,
The third part is for situations where the individual dice scores are awarded 1 point if they match the round number:
IF(B2=B5,1,0)+IF(D2=B5,1,0)+IF(F2=B5,1,0)
Here the results of the three IF functions are added.
In B7 enter 0 and then the formula =B6+B7. This is a circular reference, which is permitted after the changes in Tools/Options made earlier.
Save and close the worksheet. Open the worksheet and activate the Control Toolbox (right click the toolbar area and select it). Click on the View Code icon. Select Worksheet from the left drop down list and Calculate from the right hand list. This should give you a procedure stub as follows:
Private Sub Worksheet_Calculate()
End Sub
Now copy this code into the space:
If Range("b6") = 0 Then Range("b5") = Range("b5") + 1
Else Range("b5") = Range("b5").Value
If Range("b5").Value > 6 Then
Range("b5") = 0
Range("b7") = 0
Range("b7").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+R[0]C"
End If
This code looks at cell B6, if it is currently zero then there was no score so the round number is advanced otherwise, as a score occurred, it stays the same.
It also tests whether cell B5 is greater than 6, in which case the current game is over and the values in the cells are reset. Resetting cell B5 is straightforward but cell B7 must be reset to the original formula. This is done by setting the value to 0 and then entering the formula in row-column format. Cell B7 is selected and then a formula is inserted which adds the cell above (R[-1]C) to the current cell (R[0]C). R[-1]C means the cell above the current one in the same column, R[0]C means the current cell in the same column. The cell below the current selected cell would be R[1]C.
For a final touch the sheet can be protected against changes by double clicking on This Workbook in the VB editor left panel and entering this code:
Private Sub Workbook_Open()
Sheets("Sheet1").Protect UserInterfaceOnly:=True
End Sub
To unprotect a worksheet choose Tools/Protection/Unprotect sheet.
Save the workbook one more time and then close it. Do not save it again unless you really need to. If you save it make sure cells B5, B6 and B7 contain their original contents - 0, the long IF function and =B6+B7, which should be zero
To play the game press the F9 key. It's a little difficult to follow at first. The first press of F9 advances the round counter to 1 as it starts at zero and the dice scores cannot match this. When you press F9 again you roll your first real dice in the game. If one or more of your dice match the round number (1) you will see a score in B6 and B7. You may now roll the dice again with no change of round number - as long as you keep scoring the round number stays the same. If you fail to get a match your score is zero so the round number increases. You may see a match in the dice with the new round number but you should not see a match with the previous round scores because that is why the round counter advanced. This seems a little tricky at first but it is working correctly. Next time you roll the dice the results will be compared with the new round number and the same evaluation will take place.
Craps is a more complex dice game than the one above. Played with two dice the rules are as follows:
Set up calculation and iteration as for the previous game.
Set up cells as follows:
Now you can press the F9 key to play. Use VBA to set up cell protection if you wish. To reset the wins and losses you will have to enter 0 in D21 and D22 and then re-enter the formulae - cut and paste them.
Press the Back Button to return