Hi, I have a simple roulette simulator which generates 10,000 spins in excel using random generate between 0 and 36.
I have been trying to quantify some of the results, such as finding out throughout the 10,000 spins, how many times a black number or red number shows up 5 times in a row. And the same again for 6 times, 7 times and so on.
So basically i would like another part of the spreadsheet to read, for example:
No. of spins: 10,000
No. of times there were 5 of same colour in a row: 48
No. of times there were 6 of same colour in a row: 27
No. of times there were 7 of same colour in a row: 13
No. of times there were 8 of same colour in a row: 6
No. of times there were 9 of same colour in a row: 4
No. of times there were 10 of same colour in a row: 3
I was wondering if any of you may be able to help me figure out how to do this.
PS. i've attached the file, and just so you know reds and blacks are not assigned to even and odd numbers they are:
Red (1,3,5,7,9,12,14,16,18,19,21,23,25,27,30,32,34,36)
Black (2,4,6,8,10,11,13,15,17,20,22,24,26,28,29,31,33,35)
I would appreciate any help you could give me.
Stephen
This "array formula" will give the count of runs of 5 reds
=SUM(IF(FREQUENCY(IF(ISNUMBER(MATCH($B$1:$B$10000,$J$2:$J$19,0)),ROW($B$1:$B$10000)), IF(ISNA(MATCH($B$1:$B$10000,$J$2:$J$19,0)),ROW($B$1:$B$10000)))=5,1))
confirmed with CTRL+SHIFT+ENTER
Where B1:B10000 contains the randomly generated spins and J2:J19 contains the list of red numbers
See attached
Last edited by daddylonglegs; 08-24-2008 at 09:52 AM.
confirmed with ctrl+shift+ enter=SUM(--(FREQUENCY(IF(A1:A35="red",ROW(A1:A35)),IF(A1:A35<>"red",ROW(A1:A35)))=5))
will give you number of times 5 consecutive reds are found
change range to suit and the last digit is the length of sequence you wish to find
but first change all thos numbers to either red or black using
=IF(OR(A1={1,3,5,7,9,12,14,16,18,19,21,23,25,27,30,32,34,36}),"red","black")
Hi again guys,
Thanks so much for those solutions. Really appreciated!!!!!!!!
I was wondering as well if there was a way that, instead of giving the number of consecutive Reds or Blacks in a row, it could tell me how many spins have passed without either a Red or a Black so that Zero's can be taken into the equation.
For example, a sequence of B,R,R,R,0,R,R,R,0,0,R,B would show that 10 spins have passed without a Black number, but with the last worksheet only two sets of 3 consecutive Reds would have shown up.
If there was anyway this could be achieved i would really appreciate it.
Stephen
=SUM(--(FREQUENCY(IF(A1:A35<>"B",ROW(A1:A35)),IF(A1:A35="B",ROW(A1:A35)))=10))
would tell you the number of times there were sequences of EXACTLY 10 when no black spun
again array entered
Dont forget you could Change =10 to say =B1 and put in b1 the number of times you want checked instead
or even
in row 1
=SUM(--(FREQUENCY(IF($A$1:$A$35<>"B",ROW($A$1:$A$35)),IF($A$1:$A$35="B",ROW($A$1:$A$35)))=ROW()))
again array entered and dragged down this would increment by 1 for each row dragged down so you could see at a glance by looking at row 10
number of times there were sequences of EXACTLY 10 when no black spun row 3 would show the same for sequences of 3 and so on
Last edited by martindwilson; 01-28-2009 at 10:15 AM.
Adapted from http://mathworld.wolfram.com/Run.htm...m.com/Run.html
In a trial of N spins, if you put a 1 beside the first Black in a series of Blacks, a 2 beside the second Black, a 3 beside the next, ... then the number of expected m's (m=1, 2, 3, ...)
=N * (1 - p) * p ^ m where p = 18/37 for a roulette wheel.
This yields the expectation in the second column below:
As expected, the total is (approximately) the total number of expected Black in 10,000 spins (= 10,000 * 18/37; there are small probabilities beyond 20 consecutive Blacks). These are runs of m or more blacks; so to calculate runs of exactly m Blacks followed by a White or Red, subtract adjacent rows as in the third column.--H-- ----I---- ----J---- ----------------K----------------- ------L------- ------M------- 19 m m or more Exactly m Formulas Test m or more Test Exactly m 20 1 2,498.17 1,282.85 I20 and down: =N * (1 - p) * p ^ m 2,555 1,323 21 2 1,215.33 624.09 J20 and down: =I20 - I21 1,232 637 22 3 591.24 303.61 595 316 23 4 287.63 147.70 279 151 24 5 139.93 71.86 128 61 25 6 68.07 34.96 67 29 26 7 33.12 17.01 38 17 27 8 16.11 8.27 21 10 28 9 7.84 4.02 11 6 29 10 3.81 1.96 5 2 30 11 1.85 0.95 3 1 31 12 0.90 0.46 2 1 32 13 0.44 0.23 1 0 33 14 0.21 0.11 1 1 34 15 0.10 0.05 0 0 35 16 0.05 0.03 0 0 36 17 0.02 0.01 0 0 37 18 0.01 0.01 0 0 38 19 0.01 0.00 0 0 39 20 0.00 0 0 40 Total 4,864.86
If you take the sumproduct of the m and Exactly m columns, it totals (approximately, for the same reason) the same result.
Column J numbers dovetail pretty well with your trial. The last two columns show the results of my 10,000-spin run.
Last edited by shg; 01-28-2009 at 12:18 PM.
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
Thanks for the help again guys.
do any of u know how to formulate the following scenario:
to count how many times 8 reds occur in a row, followed by a zero, followed by a red
to count how many times 8 reds occur in a row, followed by a zero, followed by a black
to count how many times 9 reds occur in a row, followed by a zero, followed by a red.
to count how many times 9 reds occur in a row, followed by a zero, followed by a black.
etc. etc.
It would be great if somebody could update this attachment as i have a little trouble understanding the longer formulas.
All help much appreciated.
Stephen
Hi MartinOriginally Posted by martindwilson
If its not too much bother could i trouble you to enter that formula into my attached workbook. I'm a bit of a newbie with this whole excel thing, and when i copy and paste your formula it comes up with errors and doesn't seem to work. What you have explained is exactly what i want to see but i want to be able to quantify it so that i can have a table showing how many times there were sequences of exactly 10 when no black showed up for example.
Again, i'm grateful for any help.
Cheers
Stephen
stephen as requested but i dont know how to do
those! (and i cant open xlsx from work)to count how many times 8 reds occur in a row, followed by a zero, followed by a black
Hi Martin
Thanks again for your help. Unfortunately I don't seem to be able to paste that formula into my workbook (not sure why it doesnt work- as i said i'm at a very basic level with excel...)
I've attached another workbook and saved it this time as an .xls file, it shows exactly the set-up i'm trying to achieve but obviously doesn't have the formula to calculate the number of times a sequence without, say, 7 reds occurs.
Without trying to pester you, I would really appreciate it if you could enter that formula so that it works in the table i have set up. Instead of the "R" and "B", i've tried to include the numbers and the column to the right shows which numbers are Red and which are Black.
I would be grateful of any help and sorry again for the hassle.
Regards,
Stephen.
this seems to work. notice i added column to give red/ black /zero based on numbers on a standard single zero wheel which the formula checks against.
and some pretty conditional formattingoh i think i left calculation set to manual so hit f9 to recalc. or set it back to auto
Last edited by martindwilson; 01-31-2009 at 03:21 PM.
How do you change it to 1000 times in a row instead of 100?
I have created a simple roulette program that just randomizes numbers from 0 to 37 in Row A. I use the number 37 to represent the "double 00". It creates 1000 random numbers in column A.
I would like to to create column B such that if the random number generated in column A is greater than 12 but less than 37 you win your bet back + 1/2 your bet. In other words if you bet $1.00 you would increase you pot by 50 cents. However if you lost you would then decrease the pot by $2.00 and your next bet would double to $2.00. Each time you won your next be would revert back to $1.00 and start over. This is generally like the doubling ploy many gamblers use. Below is an example of what the spreadsheet would look like:Bet Spin Result Pot $500 Initial pot $1 5 Loss $499 Startng bet $1.00, You spin a 5 and lose since 5 is 12 or less $2 18 Win $502 Since you lost your bet doubles. 18 spin. Win 1.5 x bet $1 24 Win $503.50 Since you won, you bet $1. You win again 1.5X bet $1 0 Loss $502.50 Since you won you bet $1. You lose on 0. You lose $1.00 $2 3 Loss $500.50 Since you lost youble double bet. You lose again. Loss $2.00 $4 6 Loss $496.50 Double bet to $4.00. You lose. Loss $4.00 $8 9 Loss $488.50 Double bet to $8.00. You lose. Loss $8.00 $16 34 Win $512.50 Double bet to $16.00. You win. Win $24.00 $1 27 Win $514 Back to base bet since you won. Win $1.00 $1 29 Win $515 Back to base bet since you won. Win $1.00
Staring pot $100
Also I need to revert back to base bet of $1.00 if there are 5loss in a row. Any ideas would be greatly appreciated.
Last edited by shg; 07-31-2010 at 02:29 PM.
Welcome to the forum.
Please take a few minutes to read the forum rules, and then start your own thread.
Thanks.
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
Hi stephenhughes99
I wonder if you can confirm something for me. I'm new to this forum and would like to know the following:
The data in connection to the 10000 spins, is this based on a real 'Auto Spin' / 'Live Dealer' results or is it based on a computer programme?
Also - 0 (start) and 12 (in) shows as only coming in the once out of 10000 spins - is this correct?
Look forward to receiving your reply.
Thanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks