Active Topics Memberlist Search Help  
Register Login 
Lotto Number Analysis Using Excel, Access and Visual Basic  
LottoPoster Forums : LOTTO PROBABILITY, COVERAGE AND PROGRAMMING : Lotto Number Analysis Using Excel, Access and Visual Basic 
Topic: Random Lotto Numbers using the Mersenne Twister  
Author  Message  
Colin F
Lotto Systems Tester Creator & Analyst To dream the impossible dream ... Joined: September 30 2004 Location: Australia Online Status: Offline Posts: 678 
Topic: Random Lotto Numbers using the Mersenne Twister Posted: January 22 2011 at 9:49pm 

Using the Rnd function in VBA to generate a Lotto number to play after seeding with Randomize is OK for a few lines but for creating a test base or doing test runs can be a problem for if the code cycle is very tight you can end up with excessive repeat numbers and even too many consecutive repeats. The way around this is to introduce some kind of random timing between calls to the Rnd function as I did and this produces results that are indistinguishable from jumbled numbered ping pong balls.
To go more sophisticated and get fast results you can use the Mersenne Twister algorithm in VBA adapted from the C code written by Takuji Nishimura and Makoto Matsumo from this site
Copy the code and comments by Pablo Mariano Ronchi written September 12 2005 and paste it directly into a module (mt19937ar suggested).
Included in the code is a sample implementation called Sub Main which I suggest you make public to try it out calling from the debug window. It will print to a text file named mt19937arVBTest 1000 results from genrand_int32() and then 1000 numbers to 8 decimal places using the genrand_real2() function which can be used in the usual way eg Int((49 * genrand_real2) + 1).
I ran a simple routine first using the Randomize and Rnd function to initialize init_genrand() and then making sure for a line each integer was unique produced 100,000 6/49 numbers in a few seconds using genrand_real2() which includes 0.0 but excludes 1.0 to give the following Integer Group, Count and Sort.
Combining the various 6/49 Lotto game results from around the world into my AllWorld database we get some 21000 lines with about 20 repeats.
Generating 14,000,000 random numbers in VBA using the Mersenne Twister code as in the link above we get for 21000 Combinations about the same number of repeats and for other counts as in the table below. Only 8,845,381 or 63% are distinct from the 14 million generated. (Interestingly this is the same as 1 (1/e) where e = 2.718218.)
A reasonable question to ask given that we have only 8,845,381 distinct combinations after generating 14,000,000 is, "Does this have an effect on probability calculations where the 13,983,816 possible combinations are plugged in?"
One such calculation is how many combinations need to be generated to have a 50% chance of one of the combinations being duplicated as in the birthday problem which in a nutshell says on average 23 people randomly congregated are needed to get at least two people with the same birthday. A quick approximation can be obtained by taking the square root of the possibilities and adding 17% to that result to give 22. Applying the same rough calculation to a 6/49 Lotto game we get 4375 after getting the square root of 13,983,816 = 3739.5 and adding 17% of that which is 635.7.
A more "exact" figure according to the formulae of 4403 can be obtained here  simply change the figure 365 to 13983816. Have a play with the calculator like changing the probability of a match to 99.9999999999 which is converted to 1 or certainty and you get a figure of 27,799. It would be a fool indeed who thought that by playing the history they had a 50% chance of winning the lottery when their 6/49 Lotto game had a history of 4403 draws. Leaving the probability of a match at 1 change the total number of items to 49 to get items required for a match of 53! Why do I think there will certainly be a match for at most 50?
For the 14 million set generated the first duplicate occurrs at 4016 combs. A few quick sample generations could give as it did for me a figure of 4,000 as being around the mark which is well below the quick calculation. An increased number of samples could give, as it did for me, a figure spot on with the quick calculation of 4,375.
However, if we divide the 14,000,000 combinations of six integers generated using the Mersenne Twister code from a pool of 49 into 2,800 segments so that the first segment starts at 1 the second at 5,000 etc and test each one we get a considerably higher number of combinations as being required. For 4575 combinations I found 1409 had at least one duplicate and 1391 had none. ie 1409/2800 * 100 = 50.3%. Testing for 4550 gave 1391/2800 * 100 = 49.7% with at least one duplicate. Each test took well less than a minute to run.
This begs the question as to how accurate some of the probability formulaes are that are taken for granted, given that the Mersenne Twister random results receive nothing but praise?
Colin Fairbrother


Lotto Draws have no relationship to one another; the integers serve just as identifiers. Any prediction calculation on one history of draws for a same type game is just as irrelevant as another.


IP Logged  
Forum Jump 
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum 