Dice and Coins

Simulating Dice and Coins … Normal and Loaded

Introduction

As part of my final session this week I needed to introduce the idea of a Monte Carlo Simulation and the best way to do the stochastic part of that is to simulate the tossing of a coin and the rolling of a die. This page takes you through the bare bones of that but with a twist: I am going to include modelling loaded dice!

Monte Carlo Simulation

The MCS is a very grand title for what this page is about but in essence it is one! What we do is this:

Appreciate for a coin that it will land heads or tails

Then we can create a random number using, eg, RANDBETWEEN that will be either 1 or 2

Assign 1 to mean that the coin lands heads up and 2 to mean it’s tails

Like this

A1 Random Number

B1 Result

A2 =RANDBTWEEN(1,2)

B2 =IF(A1=1,”Head”,”Tail”)

That’s your column headers and first trial or iteration

Copy cells A2:B2 down to create a 10 trial run or 100 trials or 1,000 trials. It will look like this:

Random Number	Result
2	T
2	T
1	T
1	H
2	H
1	H
2	T

The purpose of this exercise is to learn how to program an MCS so let’s finish this part by summarising our data. We do that by creating a frequency distribution table and then a histogram: you can see exactly what I did by downloading the Excel file that I have created for this. See the tab 2coins and 3coins for the details. The link to the file is at the bottom of this page.

Frequency Distribution

The frequency distribution table for the coins is very simple to create as there are relatively few outcomes. In the case of tossing two coins at the same time, the results are as shown in the following table, together with the COUNTIF function I used to find the frequencies for that table:

G5 Outcome

H5 Frequency

G6 HH

H6 =COUNTIF(score,”=”&G6)

Where score is the range name for the result of having tossed the two coins: found in the range E6:E1005 in the tab 2coins. That’s a simulation of 1,000 tosses of the coins. We used COUNTIF because the results are letters and not values.

Create a 2D Column Chart and call it a histogram from the table that begins in G5!

Things are a little more complicated for the 3coins tab, in which we toss three coins at a time since there can be 8 outcomes for that and so the results table is a little bigger and the histogram is a little more complicated. Nothing onerous though and check the Excel file to see what I did.

The Dice

Tossing coins in our virtual world is fine but rolling the dice is more interesting since I programmed some loaded dice as well as normal ones!

The outcomes tab in the Excel file shows you how many outcomes there can be when rolling two dice together so that there is no doubt about what we are simulating.

The dice tab shows a two dice simulation that I won’t explain here since it is very similar to the coin tossing. You create random numbers for each die, from 1 to 6 and then add them together to get the score. The frequency distribution is the same as for the coins except larger and since we are dealing with number scores rather than letter scores we use the FREQUENCY function o count how many results equalled 2, how many were 3 and so on up to 12, the maximum score.

Here are the results of one iteration of the simulation:

Bin	f
2	23
3	62
4	99
5	113
6	128
7	157
8	140
9	100
10	93
11	58
12	27
	0
	1000

It’s not perfect but there is no reason why it should be, even after 1,000 throws of the dice!

On the dice tab not only will you see those results but you will see that I have built a Chi Square test to check on the validity of the results and that confirms we did the right thing! I won’t explain that here, to save time and space but it is there in full!

Loaded Dice

How to simulate loaded dice? Why simulate loaded dice you might also ask. Well, in real life strange things happen even if you and I would never even dream of loading a die! Things become biased for all sorts of reasons and what we did here is to simulate something being abnormal to some degree: loaded dice!

On the loaded_dice tab you will see a slightly different approach to finding the result of rolling the dice. For example, the random number and results table looks like this, starting in cell A4:

Random 1	Random 2	Die 1	Die 2	Total Spots
=RANDBETWEEN(1,9999999)	=RANDBETWEEN(1,9999999)	=VLOOKUP(A5,$P$33:$Q$38,2)	=VLOOKUP(B5,$P$33:$Q$38,2)	=C5+D5
9541139	2691094	6	2	8
5664831	7586185	4	5	9
9504902	444343	6	1	7
3931675	7783701	3	5	8

Firstly, why did we generate such large random numbers? We did that to give us a lot of flexibility and sensitivity in the load we placed on the dice. If we’d just used 1,6, it would have been been very difficult to fine tune anything.

The lookup table looks like this, starting in cell P32:

Loaded Lookup Table
–	1
1,666,000	2
3,332,000	3
4,998,000	4
6,664,000	5
8,000,000	6

The loading comes with number 6: can you see what we did? Is it obvious? Is it heavily loaded? Is it really loaded at all? You are free to change 8,000,000 to something larger or smaller: more or less loading!!

One reason I built the Chi Square test into this work book is to test whether I really did load the dice in a significant way. As it stands, with the 8,000,000 number in there, pressing the F9 key will show Chi Square rejects some results and accepts others. That is, I didn’t load the dice in such a way that the cheats win every time. Press the F9 key and see how many times Chi Square sys reject as opposed to accept and work out how loaded the dice are. Change 8,000,000 to 8,500,000 and see what happens. Change it to 7,500,000 … I pressed the F9 key 40 times and found Chi Square rejected the results 15 times. That’s pretty heavy odds in favour of the cheats!

The frequency distribution and histogram are also included for this tab and see what you can see!

Finally, there is a tab called dice_test which encourages you to take results from somewhere else and enter them in the range H10:H20 to see if they are loaded or biased or not.

Download the Excel file: simulations_dice_coins_trainer

 

Duncan Williamson