Introduction
As share of my final examination session this week I needed to introduce the mind of a Monte Carlo Simulation and the best way to do the stochastic part of that is to simulate the toss of a coin and the roll of a die. This page takes you through the bare bones of that but with a wind : I am going to include modelling load dice !
Monte Carlo Simulation
Reading: Dice and Coins –
The MCS is a very grand claim 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 function of this exercise is to learn how to program an MCS so let ’ s eat up this part by summarising our data. We do that by creating a frequency distribution board and then a histogram : you can see precisely what I did by downloading the Excel file that I have created for this. See the yellow journalism 2coins and 3coins for the details. The connection to the file is at the bottom of this page .
Frequency Distribution
The frequency distribution board for the coins is identical simpleton to create as there are relatively few outcomes. In the case of tossing two coins at the lapp meter, the results are as shown in the trace board, 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 compass mention for the leave of having tossed the two coins : found in the rate E6 : E1005 in the yellow journalism 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 board that begins in G5 !
Things are a little more complicate for the 3coins yellow journalism, 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 burdensome though and check the Excel file to see what I did .
The Dice
Tossing coins in our virtual populace is fine but rolling the die is more concern since I programmed some affluent cube ampere well as normal ones !
The outcomes tab in the Excel file shows you how many consequence there can be when rolling two dice together sol that there is no doubt about what we are simulating .
The cube check shows a two cube simulation that I won ’ deoxythymidine monophosphate explain here since it is very similar to the coin convulse. 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 phone number scores rather than letter scores we use the FREQUENCY function oxygen count how many results equalled 2, how many were 3 and so on up to 12, the utmost grade .
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 cause why it should be, tied after 1,000 throws of the die !
On the dice tab not alone will you see those results but you will see that I have built a Chi Square test to check on the robustness of the results and that confirms we did the right field thing ! I won ’ thymine explain that here, to save clock and space but it is there in broad !
Loaded Dice
How to simulate affluent dice ? Why model loaded dice you might besides ask. Well, in actual life strange things happen even if you and I would never evening pipe 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 cube !
On the loaded_dice tab you will see a slightly different approach to finding the solution of rolling the dice. For exercise, 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 |
first, why did we generate such large random numbers ? We did that to give us a bunch of flexibility and sensitivity in the load we placed on the die. If we ’ five hundred just used 1,6, it would have been been identical difficult to fine tune anything .
The search board 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 act 6 : can you see what we did ? Is it obvious ? Is it heavy loaded ? Is it very loaded at all ? You are free to change 8,000,000 to something larger or smaller : more or less load ! !
One reason I built the Chi Square examination into this influence record is to test whether I very did load the dice in a significant way. As it stands, with the 8,000,000 numeral in there, pressing the F9 samara will show Chi Square rejects some results and accepts others. That is, I didn ’ triiodothyronine load the dice in such a way that the cheats win every time. Press the F9 identify and see how many times Chi Square sys resist 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 ’ south pretty heavy odds in favor of the cheats !
The frequency distribution and histogram are besides included for this yellow journalism and see what you can see !
finally, there is a check 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
Leave a Comment