Analyze Lottery Numbers in Excel

[K_McIntosh]

Hey all ... my first post here ... I thought this would be a good place to start in order to clarify my understanding of some of the content re:

Analyze Lottery Numbers in Excel
at: Win the Lottery with Excel

I am motivated by both the immediate desire to come up with different ways of picking lottery numbers, as well as a desire to learn more about Excel (FYI - I'm still plunking along with Excel 2000 [9.0.8960 SP-3]) in the process - this seems like an excellent endeavor to do both.

Right out of the gate, from the above referenced page, I think I need clarification with:

"I downloaded data from the Ontario lottery site showing winning numbers for the past 3 months.

Using cut and paste, copy the numbers into a single column of data. Add a heading in cell A1.

"

At first read, this appears to work for one column of data at a time ... is it possible to do all six relevant (primary data) columns at once? ie. can we literally C & P all six columns worth of data into one single column of data ... I wouldn't have thought so ...

If we're only able to examine the frequency of a given integer being drawn on a column by column basis (ie. 1st # drawn, 2nd # drawn, ect.), my thought is that we are not seeing the frequency (or lack thereof) of partial or complete strings of six integers, as drawn historically. Or the order in which they occur, if and when they do. Apologies - it seems to me I had this latter thought expressed more clearly yesterday when I first composed this thread, however, that portion was lost due being auto-logged out and is just not coming to me as easily today, for whatever reason.

When implementing the =RANDBETWEEN function (as '=RANDBETWEEN(1,49)'), is it possible to configure the formula to select six unique integers at a time? I tried dragging the formula from one cell across six columns and found that it was occasionally possible for one (or more, although I have yet to see this) of the six integers to be duplicates.

Thanks for any help offered ... K_McIntosh

 

[barry houdini]

Assuming my "definitive" answers are correct, I still can't reconcile them with the math in post#18. Can anyone straighten me out?

It's the double counting, I believe. In post 18 the calculation was

=COMBIN(45,3)*46 = 652,710 but it turns out there are only 610,170

The first calculation is incorrect because it counts the same combination of 7 numbers multiple times, for example 1,2,3,4,5,6,7 is counted 4 times, once as part of 1,2,3,4 run with every combination of other numbers, once as part of 2,3,4,5 run.....once with 3,4,5,6....etc.

To get the correct 4+ count you could use my previous count of exactly 4 and extend that to add in exactly 5, exactly 6 and exactly 7, e.g.

=2*COMBIN(44,3)+44*COMBIN(43,3)+2*COMBIN(43,2)+43*COMBIN(42,2)+2*COMBIN(42,1)+42*COMBIN(41,1)+43

that gives you 610170

 

[shg]

I'm going to read that three times to internalize it, thank you, Barry.

 

[shg]

OK, I ran 4 10M-lottery simulations and got 0.70942%, 0.71168%, 0.71106%, and 0.71220%. I'm happy.

Barry, xenou, thanks for humoring me in an entertaining thread.

 

[jorgeM]

Wow! This thread keeps me reading until the last page, though i don't totally understand the percentage discussion :D
I'm amazed with you guys.

I'll try your codes later on my lottery database. thanks to all!

 

[jamilm]

For what it's worth, a lottery number function:

Function RandomLotteryNumber(ByVal minValue As Long, maxValue As Long, digits As Long) As String
Dim dic As Object  '//Scripting.Dictionary
Dim x As Long
Dim a, i

    Set dic = CreateObject("Scripting.Dictionary")
    
    Randomize
    Do While dic.Count < digits
        x = Int((maxValue - minValue + 1) * Rnd + minValue)
        If Not dic.Exists(CStr(x)) Then
            dic.Add CStr(x), x
        End If
    Loop
    
    a = dic.Keys
    For i = 0 To UBound(a)
        a(i) = CLng(a(i))
    Next i
    Call BubbleSort(a)
    
    RandomLotteryNumber = Join(a, "|")

End Function

Sub BubbleSort(ByRef arr)
     
Dim First As Long
Dim Last As Long
Dim i As Long, j As Long
Dim Temp As Long
     
    First = LBound(arr)
    Last = UBound(arr)
    For i = First To Last - 1
        For j = i + 1 To Last
            If arr(i) > arr(j) Then
                Temp = arr(j)
                arr(j) = arr(i)
                arr(i) = Temp
            End If
        Next j
    Next i
     
End Sub

Edit:
called as (in this case) =RandomLotteryNumber(1,49,6)
meaning, a lottery with six numbers, with the numbers between 1 and 49 inclusive.

I am just wondering that how this code generates from 1 to 49 where the code itself does not refer to any nomirical number|? can you please share your knowledge on this? is it the dictionary scripting that does the magic . I have a little bit of vba knowledge but this code seems sophisticated.

 

[xenou]

Hi,
We use the rand function to generate an integer between 1 and 49, or more specifically between whatever min and max values are provided to the function when it is called:
x = Int((maxValue - minValue + 1) * Rnd + minValue)

The VBA help file on Rand() gives this same formula, I think. As for the scripting dictionary, the main reason for it is to use it as a collection object that holds only unique numbers. If the number already exists (we've already picked it), then we try again. Dictionary keys must be unique so we store the picked numbers as dictionary keys to take advantage of this feature. My assumption is that all numbers have an equal chance of being chosen, regardless of how many numbers there are, so this is a valid way to pick numbers. In a physical model, you'd have the 49 ping ball balls, then the 48 remaining after the first pick, then 47 ....

ξ

 

[jamilm]

Hi,
We use the rand function to generate an integer between 1 and 49, or more specifically between whatever min and max values are provided to the function when it is called:
x = Int((maxValue - minValue + 1) * Rnd + minValue)

The VBA help file on Rand() gives this same formula, I think. As for the scripting dictionary, the main reason for it is to use it as a collection object that holds only unique numbers. If the number already exists (we've already picked it), then we try again. Dictionary keys must be unique so we store the picked numbers as dictionary keys to take advantage of this feature. My assumption is that all numbers have an equal chance of being chosen, regardless of how many numbers there are, so this is a valid way to pick numbers. In a physical model, you'd have the 49 ping ball balls, then the 48 remaining after the first pick, then 47 ....

ξ

thank you very much.

 

[xenou]

No problem. I'd forgotten about this thread. It was a fun investigation.
ξ

 

[sandi84]

I have a set of lottery numbers. The lottery is 7/39 (Seven numbers are drawn from a pool of 39 numbers). I have last 52 draws

DRAW	NUMBER 1	NUMBER 2	NUMBER 3	NUMBER 4	NUMBER 5	NUMBER 6	NUMBER 7
1	2	3	5	7	26	31	35
2	7	12	13	21	24	33	38
3	1	4	6	21	23	28	34
4	10	14	17	19	21	26	27
5	8	12	22	32	34	37	38
6	1	5	15	21	26	30	38
7	11	14	19	23	24	27	29
8	1	4	6	7	10	27	36
9	8	14	17	22	23	35	39
10	1	16	18	26	27	34	38
11	3	6	19	24	34	35	39
12	1	10	11	18	19	28	34
13	1	4	9	12	13	14	37
14	11	12	23	24	31	32	35
15	2	8	11	17	28	32	36
16	10	17	21	25	27	31	34
17	5	21	24	25	34	37	38
18	9	20	25	31	32	36	39
19	8	23	24	25	29	35	38
20	14	16	23	27	36	38	39
21	4	8	17	27	28	37	38
22	4	6	9	12	18	19	33
23	6	16	18	23	26	30	33
24	11	15	19	27	33	34	38
25	2	7	10	21	24	26	29
26	8	9	15	17	27	29	38
27	1	12	18	22	28	29	39
28	1	6	11	13	21	24	39
29	1	11	19	31	33	34	35
30	2	4	14	15	30	31	35
31	6	10	13	19	25	36	38
32	6	7	8	11	12	17	38
33	3	6	9	10	15	23	33
34	4	9	17	24	29	34	35
35	9	10	19	22	23	37	39
36	2	3	9	11	13	14	15
37	3	8	12	15	23	25	34
38	12	13	16	20	24	25	35
39	5	10	16	20	22	29	30
40	2	11	14	15	28	29	32
41	11	11	12	14	26	28	36
42	14	16	18	26	27	28	39
43	9	11	13	30	31	32	38
44	9	20	23	25	27	33	36
45	1	16	19	26	28	32	38
46	1	3	7	12	17	25	29
47	12	15	20	21	25	35	38
48	1	17	22	27	29	34	38
49	1	2	12	26	33	36	37
50	3	5	7	8	19	23	29
51	1	5	9	19	27	28	35
52	4	5	6	16	25	27	29

<colgroup><col><col span="7"></colgroup><tbody>
</tbody>

My question is ... is there a formula to figure out which set of numbers to play, that gives the most jackpot wins (all 7 numbers) from this 52 draws.

 

[mole999]

its incalculable for anything meaningful that you could afford bet on (IMHO, Roulette has better odds 1:36 I think)