Wednesday 7 January 2015

Simulating Roulette in Excel

In one of my earlier posts, i showed you how to simulate a coin toss in excel (or google docs)

In this article, we will look at extending that technique to allow us to simulate roulette.   We're obviously just gonna randomly throw a roulette number and not simulate physics or mechanics (for now ;).   The idea is that you will be able to use this simulator to test out any crazy betting systems you may come up with.

This technique will work for both European and American wheels.   So let's get to it.

Setting up our wheel

First we want to do is setup our roulette wheel in Excel, and it should look something like this...



  • First column is a list of numbers from 0 to 36 (37 if you want to do an American Wheel)
  • Second column is the corresponding number on the roulette wheel (following actual layout)
  • Third column is the color of the number

So to set it up, do the following

1) Open Excel
2) Rename the tab to "European Wheel"
3) In the first row enter the headers (as shown in the screenshot): Number, Actual Number Color
4) In column A enter in each row the numbers 0 to 36
5) Enter the corresponding number in the roulette wheel in order in Column B
6) Enter the color of the actual number in Column C

European Wheel

The following table gives the layout of a European Wheel, you can just copy and past it into excel


Number Actual Number Color
0 0 Green
1 32 Red
2 15 Black
3 19 Red
4 4 Black
5 21 Red
6 2 Black
7 25 Red
8 17 Black
9 34 Red
10 6 Black
11 27 Red
12 13 Black
13 36 Red
14 11 Black
15 30 Red
16 8 Black
17 23 Red
18 10 Black
19 5 Red
20 24 Black
21 16 Red
22 33 Black
23 1 Red
24 20 Black
25 14 Red
26 31 Black
27 9 Red
28 22 Black
29 18 Red
30 29 Black
31 7 Red
32 28 Black
33 12 Red
34 35 Black
35 3 Red
36 26 Black



American Wheel

Setting up an American wheel is also pretty easy.  Just create a new table and name it "American Wheel" and set it up the same as a European wheel (0..37 though) and paste in the following layout


Number Actual Number Color
0 0 Green
1 28 Black
2 9 Red
3 26 Black
4 30 Red
5 11 Black
6 7 Red
7 20 Black
8 32 Red
9 17 Black
10 5 Red
11 22 Black
12 34 Red
13 15 Black
14 3 Red
15 24 Black
16 36 Red
17 13 Black
18 1 Red
19 00 Green
20 27 Red
21 10 Black
22 25 Red
23 29 Black
24 12 Red
25 8 Black
26 19 Red
27 31 Black
28 18 Red
29 6 Black
30 21 Red
31 33 Black
32 16 Red
33 4 Black
34 23 Red
35 35 Black
36 14 Red
37 2 Black

Setting up the random number generator

So we have our wheels setup, so we just need to setup our random number generator.   Which we want to look something like this



In this diagram:
  • Column A shows our randomly roulette number
  • Column B shows the color of the number corresponding to it's color on the European Wheel
This is pretty easy to setup
  • Manually enter the headers (Winning Number, Winning Color)
  • In row 2, enter the following formula in Column A
=VLOOKUP(RANDBETWEEN(0,36),'European Wheel'!$A$2:$A$38,1)

This basically generates a random number between 0 and 36 (RANDBETWEEN(0,36).   And then looks up the corresponding number on our European Wheel tab.   If we want to do this for an American Wheel, we would change the tab to American Wheel and set RANDBETWEEN to generate numbers between 0 and 37,
  • Enter the following formula in Row 2 of Column B
=VLOOKUP(A3,'European Wheel'!$A$2:$C$38,3)

This will look up the color of the generated number in our European Wheel (again if you want to simulate American Wheels then switch the tab to American Wheel.
  • Do the draggy extends paste thing to copy it to multiple rows

Voila, one very simple roulette simulator in Excel.

This is very simple and easily allows you test betting plans etc.   I use it for disproving systems such as martingale.   It usually proves it to fail pretty quickly.   I obviously have more advanced versions which can do billions of rows of testing but Excel isn't so suitable for that.    However this should be pretty suitable for your needs.

In a future post, i'll show you how to test staking plans such as Martingale and Reverse Martingale with the simulator.   So you can see for yourself how ridiculous these staking plans are.

Hope this helps

UPDATE

You can download a copy of my simulator from my dropbox

https://www.dropbox.com/s/3btgpurbtsuof50/Roulette%20Simulator.xlsx?dl=0



2 comments:

  1. Enter the following formula in Row 2 of Column B
    =VLOOKUP(A3,'European Wheel'!$A$2:$C$38,3)

    Should that not be =VLOOKUP(A2 ???

    feel free to delete this post, if I am wrong, or if you edit the content to the correct A2 if i am right.. or leave it for future ridicule or acknowledgement!!

    ReplyDelete
  2. If you good with theoretical mathematics, you can roll into gambling and make money from it. With bob casino review you can try to do it.

    ReplyDelete