Making a Dice Simulator in Excel

Open a new Excel file and rename the worksheets Input, Process and Output as below. Change the colours of the tabs if you wish.

/ You will create this spreadsheet on the Input sheet.
The formula for each throw of the dice (the ones highlighted by the red square) is…
=ROUND((RAND()*(6-1)+1),0)
This gives you a whole number (also called an integer) between 1 and 6.
The formula for the Total Score (the ones highlighted in blue) is Dice1 + Dice2.
Drag this formula down.

Now onto the Process tab…

/ This will count how many times each number occurs for each dice.
The formula for cell B2 would be
=COUNTIF(Input!B4:B13,1)
The formula for B3 would be
=COUNTIF(Input!B4:B13,2)
The formula for C2 would be
=COUNTIF(Input!C4:C13,1)
The formula for C3 would be
=COUNTIF(Input!C4:C13,2)

Try and work out the rest of the CountIf formulas.


Now for the Output Tab. Click onto the Output tab and click on the Chart Wizard icon on the toolbar.

/ Choose a Column chart and click Next
/ In the next box click on the square next to data range.
/ In the data range box click on the Process sheet and select the range A1:C7
This is quite tricky so be careful.
Once you’ve done that click on the square at the end again.
/ Then click on Next
/ Now add in the following titles
Chart title:
Number Frequency for 10 Throws with 2 Dice
X axis
Dice Number
Y axis
Occurrences

Your graph should now look like below….

If you keep pressing F9 to refresh the formulas then you should see the graph change. Is there a pattern? Or is it just like soooo random????

Can you predict a random event?

Make sure you save this Spreadsheet in your Year 8 ICT folder.

Name it Dice Simulator