How to simulate a distribution and use the lottery's own randomness against itself.Prev TopicNext Topic

• JADELottery

  

  Native American Eagle Sun

  MN
  United States
  Member #21
  December 7, 2001
  4,811 Posts
  Offline
  May 22, 2015, 11:16 am

  ▲▼

  We're going to start posting in a bit.

  We ask that you hold on any questions till we are finished.

  If we have more to to post, you'll see a 'Continues...' at the bottom of the post.

  When we are done, we will post '...Done.' at the bottom of the page.

  This takes several posts because of the large number of graphs and tables.

  We tried to post in one gigantic post once and it caused problems loading.

  In the end we will be posting an Excel Function that can be copied and pasted into Excel to be used in Excel or modified to suite any programming language you'd like to use.

  Continues...

  The One Over None
  I Know...
• JADELottery

  

  Native American Eagle Sun

  MN
  United States
  Member #21
  December 7, 2001
  4,811 Posts
  Offline
  May 22, 2015, 11:46 am

  ▲▼

  We will use Mega Millions' Mega Ball and the Three Sum topic we posted as an example.

  The Mega Ball Three Sum distribution table is displayed below with a chart.

  Three Sum	Count
  1	0
  2	0
  3	1
  4	3
  5	6
  6	10
  7	15
  8	21
  9	28
  10	36
  11	45
  12	55
  13	66
  14	78
  15	91
  16	105
  17	120
  18	133
  19	144
  20	153
  21	160
  22	165
  23	168
  24	169
  25	168
  26	165
  27	160
  28	153
  29	144
  30	133
  31	120
  32	105
  33	91
  34	78
  35	66
  36	55
  37	45
  38	36
  39	28
  40	21
  41	15
  42	10
  43	6
  44	3
  45	1

   

  

   

  Given the distribution table, the first step in simulating this distribution is the integrate the table by adding the lowest count to the next higher one.

  Continues...

  The One Over None
  I Know...
• JADELottery

  

  Native American Eagle Sun

  MN
  United States
  Member #21
  December 7, 2001
  4,811 Posts
  Offline
  May 22, 2015, 11:49 am

  ▲▼

  The table below show the sum Count and the Integration.

  Three Sum	Count	Integrated
  1	0	0
  2	0	0
  3	1	1
  4	3	4
  5	6	10
  6	10	20
  7	15	35
  8	21	56
  9	28	84
  10	36	120
  11	45	165
  12	55	220
  13	66	286
  14	78	364
  15	91	455
  16	105	560
  17	120	680
  18	133	813
  19	144	957
  20	153	1110
  21	160	1270
  22	165	1435
  23	168	1603
  24	169	1772
  25	168	1940
  26	165	2105
  27	160	2265
  28	153	2418
  29	144	2562
  30	133	2695
  31	120	2815
  32	105	2920
  33	91	3011
  34	78	3089
  35	66	3155
  36	55	3210
  37	45	3255
  38	36	3291
  39	28	3319
  40	21	3340
  41	15	3355
  42	10	3365
  43	6	3371
  44	3	3374
  45	1	3375

   

  The Integrated graph.

  

   

  Next we need to Normalize the Integration for use with random selection.

  Continues...

  The One Over None
  I Know...
• JADELottery

  

  Native American Eagle Sun

  MN
  United States
  Member #21
  December 7, 2001
  4,811 Posts
  Offline
  May 22, 2015, 11:57 am

  ▲▼

  Normalization of the Integration is simply making the values greater than or equal to 0, and less than or equal to 1.

  This is done by finding the Maximum value in the Integration and dividing all the Integration values by that Maximum.

  The table shows the Normalized Integration.

  Three Sum	Count	Integrated	Normalized
  1	0	0	0
  2	0	0	0
  3	1	1	0.000296296
  4	3	4	0.001185185
  5	6	10	0.002962963
  6	10	20	0.005925926
  7	15	35	0.01037037
  8	21	56	0.016592593
  9	28	84	0.024888889
  10	36	120	0.035555556
  11	45	165	0.048888889
  12	55	220	0.065185185
  13	66	286	0.084740741
  14	78	364	0.107851852
  15	91	455	0.134814815
  16	105	560	0.165925926
  17	120	680	0.201481481
  18	133	813	0.240888889
  19	144	957	0.283555556
  20	153	1110	0.328888889
  21	160	1270	0.376296296
  22	165	1435	0.425185185
  23	168	1603	0.474962963
  24	169	1772	0.525037037
  25	168	1940	0.574814815
  26	165	2105	0.623703704
  27	160	2265	0.671111111
  28	153	2418	0.716444444
  29	144	2562	0.759111111
  30	133	2695	0.798518519
  31	120	2815	0.834074074
  32	105	2920	0.865185185
  33	91	3011	0.892148148
  34	78	3089	0.915259259
  35	66	3155	0.934814815
  36	55	3210	0.951111111
  37	45	3255	0.964444444
  38	36	3291	0.975111111
  39	28	3319	0.983407407
  40	21	3340	0.98962963
  41	15	3355	0.994074074
  42	10	3365	0.997037037
  43	6	3371	0.998814815
  44	3	3374	0.999703704
  45	1	3375	1

  

   

  Now we can use the Normalized table with a random number generator to simulate a Three Sum selection.

  Continues...

  The One Over None
  I Know...
• JADELottery

  

  Native American Eagle Sun

  MN
  United States
  Member #21
  December 7, 2001
  4,811 Posts
  Offline
  May 22, 2015, 12:06 pm

  ▲▼

  Most computers have a random generator that can spit out a value that is greater than or equal to 0, and less than 1.

  This works Great, because all we have do to find a properly selected Three Sum based on a known distribution is to do a reverse find using the random number and the Normalized Integration.

  The graph below shows how we start at the left with some random place in the Y axis (Maximum Proportional) and move to the bar graph then move down to the X axis (Three Sum) to find the sum.

  The Red, Green and Blue arrows show some random selections.

  

  Continues...

  The One Over None
  I Know...
• JADELottery

  

  Native American Eagle Sun

  MN
  United States
  Member #21
  December 7, 2001
  4,811 Posts
  Offline
  May 22, 2015, 12:16 pm

  ▲▼

  So, how well does this work?

  We have setup a function in Excel that takes the Three Sums and its Distribution, then randomly selects a sum based on that distribution.

  We simulated 1,500 samples to see how well this would fit the know distribution.

  For 1,500 samples, the theoretical distribution is displayed in the graph below.

  

   

  Our 1,500 sample run produced the following distribution.

  

   

  Continues...

  The One Over None
  I Know...
• JADELottery

  

  Native American Eagle Sun

  MN
  United States
  Member #21
  December 7, 2001
  4,811 Posts
  Offline
  May 22, 2015, 12:23 pm

  ▲▼

  Now the Excel Function, Here It IS!!!

  ____________________________________________________________________________________________________

  Function Disimulate(ByVal theNumbers As Range, ByVal theDistribution As Range) As Variant
  On Error GoTo exitfunction
  If (theNumbers.Columns.Count > 1) Or (theDistribution.Columns.Count > 1) Or (theNumbers.Rows.Count <> theDistribution.Rows.Count) Then Disimulate = 0
  Dim a As Long
  Dim d(), r, sum As Double
  ReDim d(theDistribution.Rows.Count)
  d(0) = 0: sum = 0
  For a = 1 To theDistribution.Rows.Count
  sum = d(a - 1) + theDistribution.Cells(a, 1)
  d(a) = sum
  Next a
  For a = 0 To theDistribution.Rows.Count
  d(a) = d(a) / sum
  Next a
  r = Rnd()
  a = 0
  Do
  a = a + 1
  Loop Until (r >= d(a - 1)) And (r < d(a))
  Disimulate = theNumbers.Cells(a, 1)
  Exit Function
  exitfunction:
  Disimulate = 0
  End Function

  ____________________________________________________________________________________________________

  Just copy and paste what's between the lines into Excel for those that know what they are doing.

  There's more, yep.

  Continues...

  The One Over None
  I Know...
• JADELottery

  

  Native American Eagle Sun

  MN
  United States
  Member #21
  December 7, 2001
  4,811 Posts
  Offline
  May 22, 2015, 12:34 pm

  ▲▼

  How's it work?

  The function is =Disimulate(Numbers, Distribution)

  The numbers you're looking to select are a single column in the sheet placed in you guessed it, Numbers.

  The distribution you're looking to simulate goes in, yep, Distribution.

  Don't for get to lock the position of the Numbers and Distribution cells using '$' like $A$1:$A$45 to prevent it from moving when copying the Disimulate function to another cell.

  The function will kick out a Number at random once.

  If you need to get more selection in the same place, copy over the same cells you are looking to simulate.

  Continues...

  The One Over None
  I Know...
• JADELottery

  

  Native American Eagle Sun

  MN
  United States
  Member #21
  December 7, 2001
  4,811 Posts
  Offline
  May 22, 2015, 12:45 pm

  ▲▼

  And finally, the best part of this function.

  Simulating the lottery's own random selection.

  It simple, get a distribution of numbers and plug it into the function.

  We used the Mega Ball for this demonstration.

  The current distribution of the Mega Ball as of 2015-05-22 @ 11:39 CT is:

  MB	Real
  1	12
  2	11
  3	17
  4	8
  5	8
  6	13
  7	17
  8	8
  9	10
  10	10
  11	11
  12	10
  13	8
  14	7
  15	15

   

  We ran a sample set for the same number of draws going back to 2013-10-22 and simulated the following.

  MB	Real	Simulated
  1	12	15
  2	11	15
  3	17	15
  4	8	3
  5	8	8
  6	13	15
  7	17	14
  8	8	8
  9	10	8
  10	10	8
  11	11	13
  12	10	9
  13	8	9
  14	7	7
  15	15	18

  

   

  Pretty close, Aye!

  Also, the best part about this function is no matter what the lottery does, the function adapts instantly.

  ...Done.

  The One Over None
  I Know...
• RJOh

  

  100

  mid-Ohio
  United States
  Member #9
  March 24, 2001
  20,272 Posts
  Offline
  May 22, 2015, 3:20 pm

  ▲▼
  In response to JADELottery

  Quote: Originally posted by JADELottery on May 22, 2015

  And finally, the best part of this function.

  Simulating the lottery's own random selection.

  It simple, get a distribution of numbers and plug it into the function.

  We used the Mega Ball for this demonstration.

  The current distribution of the Mega Ball as of 2015-05-22 @ 11:39 CT is:

  MB	Real
  1	12
  2	11
  3	17
  4	8
  5	8
  6	13
  7	17
  8	8
  9	10
  10	10
  11	11
  12	10
  13	8
  14	7
  15	15

   

  We ran a sample set for the same number of draws going back to 2013-10-22 and simulated the following.

  MB	Real	Simulated
  1	12	15
  2	11	15
  3	17	15
  4	8	3
  5	8	8
  6	13	15
  7	17	14
  8	8	8
  9	10	8
  10	10	8
  11	11	13
  12	10	9
  13	8	9
  14	7	7
  15	15	18

  

   

  Pretty close, Aye!

  Also, the best part about this function is no matter what the lottery does, the function adapts instantly.

  ...Done.

  Based on the above information if I was going to only play one mega ball in tonight's drawing which one would give me my best opportunity for a win on every ticket?  Picking the winning megaball means every ticket is free and I would be playing with the lottery's money.

   * you don't need to buy every combination, just the winning ones * 

         
  
• JADELottery

  

  Native American Eagle Sun

  MN
  United States
  Member #21
  December 7, 2001
  4,811 Posts
  Offline
  May 22, 2015, 3:49 pm

  ▲▼
  In response to RJOh

  Quote: Originally posted by RJOh on May 22, 2015

  Based on the above information if I was going to only play one mega ball in tonight's drawing which one would give me my best opportunity for a win on every ticket?  Picking the winning megaball means every ticket is free and I would be playing with the lottery's money.

  The function makes the selection.

  If you need an Excel file, we will have a generic one later today.

  The One Over None
  I Know...
• JADELottery

  

  Native American Eagle Sun

  MN
  United States
  Member #21
  December 7, 2001
  4,811 Posts
  Offline
  May 22, 2015, 6:11 pm

  ▲▼
  In response to JADELottery

  Quote: Originally posted by JADELottery on May 22, 2015

  The function makes the selection.

  If you need an Excel file, we will have a generic one later today.

  Here is the Disimulate Excel file for Mega Millions and Powerball.

  http://www.jadexcode.com/files/excel/disimulate-mm-pb-0.xlsm

  or

  ftp://www.jadexcode.com/excel/disimulate-mm-pb-0.xlsm

  good luck.

  The One Over None
  I Know...
• lakerben

  

  50

  New Mexico
  United States
  Member #86,096
  January 29, 2010
  24,778 Posts
  Offline
  May 22, 2015, 10:26 pm

  ▲▼
  In response to JADELottery

  Quote: Originally posted by JADELottery on May 22, 2015

  Here is the Disimulate Excel file for Mega Millions and Powerball.

  http://www.jadexcode.com/files/excel/disimulate-mm-pb-0.xlsm

  or

  ftp://www.jadexcode.com/excel/disimulate-mm-pb-0.xlsm

  good luck.

  Thanks jade!

  I'm sitting here listening to Merle Haggard sipping some coffee!

   

  
• MzDuffleBaglady

  

  25

  F-I-L-T-E-R-S
  United States
  Member #81,312
  October 16, 2009
  44,374 Posts
  Online
  May 22, 2015, 10:40 pm

  ▲▼

  Good Luck.

  Missouri pick 3, giving out humble pies!
• JADELottery

  

  Native American Eagle Sun

  MN
  United States
  Member #21
  December 7, 2001
  4,811 Posts
  Offline
  May 26, 2015, 7:48 am

  ▲▼

  No problem.

  Hope you win big.

  The One Over None
  I Know...