This tutorial demonstrates how to create a simulation of a dice throw in Excel.
The throw of a dice is a popular away of determining an outcome in board games. This random chance of a number between 1 and 6 provides that element of luck to games.
Download the completed dice throw simulation spreadsheet
Format the Dice to Show Spots
The first step is to format the cells so that they look like the side of a die.
- Select the range of cells you want to use for the dice. Cells D2:F4 have been used in this example
- Click the Border list arrow on the Home tab and select Outside Borders
- Click the Font list arrow and select Wingdings (a lowercase l in Wingdings is displayed as a spot)
Generate a Random Number Between 1 and 6
We need a function to generate a random number between 1 and 6 just like the roll of a dice. For this we need the RANDBETWEEN function.
- Select the cell you want to use to generate the random number simulated by the dice throw in Excel. Cell A1 is used in this example
- Enter the formula =RANDBETWEEN(1,6)
- This number can then be hidden by formatting the text to white
Display the Correct Number of Spots for the Number Generated
The next step is to make the dice display the correct number of spots to the number returned by the RANDBETWEEN function.
To do this we will use the IF function with the OR and AND functions to handle multiple criteria.
- Select the cells in the top left and bottom right of the dice (D2 and F4 in this example) and enter =IF(AND(A1>=2,A1<=6),”l”,””)
- Select the cells in the top right and bottom left (F2 and D4) and enter =IF(AND(A1>=4,A1<=6),”l”,””)
- Select the cells in the middle left and middle right (D3 and F3) and enter =IF(A1=6,”l”,””)
- Select the centre cell (E3) and enter =IF(OR(A1=1,A1=3,A1=5),”l”,””)
Click a Button to Perform Dice Throw in Excel
The RANDBETWEEN function will run every time you edit a cell or use a command in the spreadsheet. To limit it to the click of a button to roll the dice we need to turn off automatic calculation, and then insert a command button containing a macro to calculate the sheet.
- Click the Formulas tab on the Ribbon
- Click the Calculation Options button and select Manual
- Click the View tab on the Ribbon
- Click the list arrow on the Macros button and select Record Macro
- Enter a Macro name: and select This Workbook from the Store macro in: list
- Click Ok to begin the recording
- Click the Formulas tab and click the Calculate Sheet button
- Click the Stop Recording button on the Status Bar
- Click the Developer tab on the Ribbon
- Click the Insert button, select the Button (Form Control) and draw the button onto the sheet
- The Assign Macro dialogue box appears. Select the required macro and click Ok
The spreadsheet is complete. Each click of the button will initiate another dice throw in Excel.
Check your numbers.
The randbetween(1,6) generates an average of 3.5.
Randbetween(0,6) generates the correct average, but doesnt simulate die throws well.
Excel programming issue?
no wait…. nevermind….
I’m not so concerned with the graphics of rolling dice, but rather just getting a number. When rolling lots of dice for gaming I typically use the rand function and add it over and over. For an example of an 8 sided die 8 times: =TRUNC(RAND()*8+1)+TRUNC(RAND()*8+1)+TRUNC(RAND()*8+1)+TRUNC(RAND()*8+1)+TRUNC(RAND()*8+1)+TRUNC(RAND()*8+1)+TRUNC(RAND()*8+1)+TRUNC(RAND()*8+1)
I wish there was a repeat function for numbers similar to the one for strings that could let me repeat another number function without the repeating large formula. Let’s call this new function NUMREPT:
=NUMREPT(n,(other_function),{operator_string})
The default operator_string would be addition “+”.
I’d use it thus:
=NUMREPT(8,(TRUNC(RAND()*8+1),”+”)
Although I’d use it for rolling random numbers it should be used with other math functions.
Very nice blog post. I certainly appreciate this website. Continue the good
work!
Thank you