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

 

[mirabeau]

OK guys ... I did some reading and experimenting ...

...

And, I've got mirabeau's Code stored as (a Microsoft Visual Basic) 'Module1', under 'VBA Project (filename2.xls)' > 'Modules' in a given Workbook (filename2.xls), and, it is visible under 'Tools' > 'Macro' > 'Macros' in said Workbook ... however, when Run, it fails with "Run-time error '1004': Application-defined or object-defined error" due to me not having figured out how to integrate this 'visible' Macro properly into 'filename1.xls'.

Can I get a nudge in the right direction please?

Thanks, K_Mc

hi K_Mc,

If the error 1004 problem arises from your using an Excel version earlier than 2007, then the 100,000 lines my code as posted will overflow your worksheet and give error.

If you'd still like to test that code, and I hope you do, then change the 6th line down, which as posted is

rws = 10 ^ 5        'number of sets of lottery numbers

to

rws = 50000       'number of sets of lottery numbers

and re-run it.

Please post back if you still have an error.

If you want more such "lottery numbers" then it's easy enough to produce enough to overflow them into additional columns or additional worksheets up to the limits of your computer's memory.

However, I wouldn't see that as a useful exercise.

If your ultimate aim is to try to swing lottery odds in your favor and you have a plausible method of checking whether some numbers may occur more frequently then others, then here is a code that will give you a "loaded" or "biased", i.e. not equiprobable, set of "lottery numbers" on which to test your method

Sub biasededottery()
Range("A:A").ClearContents
Dim a() As Long, c() As String
Dim al&, n&, m&, x As String, q&
al = 1000 'change to get more or less numbers
ReDim c(1 To al, 1 To 1)
n = 49: m = 6
Randomize
For q = 1 To al
ReDim a(m)
    x = vbNullString
    For j = 1 To m
        a(j) = a(j - 1) + 1 + Int((Rnd ^ 3) * (n - m + j - a(j - 1)))
        x = x & "|" & a(j)
    Next j
c(q, 1) = Right(x, Len(x) - 1)
Next q
Cells(1).Resize(al) = c
End Sub

 

[pztexas12]

I heard investing in the lottery is a good idea.

 

[K_McIntosh]

Hey all ... working my way through all the suggestions ... thanks for all the feedback eh? ...

xenou ... I didn't try your 'bad hack' as mirabeau had already posted a Reply here by the time I looked at all the replies to my thread ...

I got an MS VB Editor Compile error from the =RandomLotteryNumber_Array(1,49,7)
Function Array Code you posted (screenshot available, stored locally - no JPG attachments allowed here? MS VB Editor debugger halts with 'Call BubbleSort' hi-lited in blue) ... is this another limitation of my version of Excel (2000, aka 9.0.8960 SP-3)? or?

chucklesl066 ... I got an MS VB Run-time error '1004': from the
euromillions_number_generator()
Sub Code you posted (screenshot issue as per above ... essentially, MS VB Editor debugger halts with yellow arrow at: 'Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove' hi-lited in yellow) ... limitation consideration as above? or?

Also, I will try to tinker with your Code to achieve 6 random integers; 1 to 49, as I am focusing on the Lotto 6/49 in Canada and don't care about the 'Extra' # they pick ...

mirabeau ... your correction worked for the generatelottery() Sub Code and your biasedlottery() Sub Code works well ... thank you for that, although I've yet to test my method in the manner you've suggested ...

pztexas12 ... **** skippy! You can't win if you don't play ... play + learn ... even better!

K_Mc

 

[xenou]

I got an MS VB Editor Compile error from the =RandomLotteryNumber_Array(1,49,7)
Function Array Code you posted (screenshot available, stored locally - no JPG attachments allowed here? MS VB Editor debugger halts with 'Call BubbleSort' hi-lited in blue) ... is this another limitation of my version of Excel (2000, aka 9.0.8960 SP-3)? or?

Hmmm. Note that there is a sub called BubbleSort() that was posted with the first RandomLotteryNumber function I wrote. This sub should be in the same standard module as the function.

 

[K_McIntosh]

xenou ... =RandomLotteryNumber_Array(1,49,7) Function Array Code working ... now that I've tacked on the BubbleSort() sub ... thank you for that ... interestingly, the first array it calculated was:

[TABLE="width: 336"]
<COLGROUP><COL style="WIDTH: 48pt" span=7 width=64><TBODY>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]7[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]14[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]21[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]45[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]46[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]47[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]48[/TD]
[/TR]
</TBODY>[/TABLE]

... an amazing coincidence to pick 4 consecutive integers, yes?

Subsequent attempts produced more random results.

K_Mc

 

[xenou]

Hi,
Well, they say the problem with random numbers is that they don't look random. In fact, the number 1,2,3,4,5,6,7 is just as likely to occur as any other, in an independent trial. Also don't forget that these numbers are sorted by the lottery algorithm, so they weren't necessarily "picked" four in a row (if you will), but out of the seven picked four were consecutive. The total number of different consecutive four-in-row numbers divided by the total number of all possible lottery results would be the actual chance of this occurring (think of it as the possibility of getting a four-card straight in a 7-handed round of poker where the "cards" are 1-49 rather than 1-13. Maybe if I have time later tonight I'll see what that comes to!

 

[mirabeau]

Hey all ... working my way through all the suggestions ... thanks for all the feedback eh? ...
...

mirabeau ... your correction worked for the generatelottery() Sub Code and your biasedlottery() Sub Code works well ... thank you for that, although I've yet to test my method in the manner you've suggested ...
...

K_Mc

Hi K_Mc,

Thanks for the feedback. Good those ones worked for you.

It occurred to me maybe you'd find it useful to have output in separate cells if you want to do further analysis on the numbers.

Here's a modification of my "unbiased" lottery draw generator that does this. Can do similarly for the "biased" generator if you like.

Sub generatelottery2()

Const l& = 1        'lower value
Const u& = 49       'upper value
Const n& = 6        'number of numbers per draw
Dim a(), b() As Boolean
Dim rws&, i&, x&, k&
rws = 20       'number of lottery draws
ReDim a(1 To rws, 1 To n)

Randomize
For i = 1 To rws
    ReDim b(l To u): k = 0: s = ""
    Do
        x = Int(Rnd * u) + l
        If Not b(x) Then k = k + 1: b(x) = True
    Loop Until k = n
    k = 0
    For x = l To u
        If b(x) Then k = k + 1: a(i, k) = x
    Next x
Next i
Range("C1").Resize(rws, n) = a
With Range("A1").Resize(rws)
    .Cells = "=""Draw "" & row()"
    .Value = .Value
End With
End Sub

 

[shg]

Hmmm.

There are combin(49, 7) = 85,900,584 possible draws.

There are 46 sequences of four ascending digits (1-2-3-4 through 46-47-48-49), and there are combin(45,3) = 14,190 ways to choose the remaining 3, so I think the chances of drawing a 4-ball (or longer) straight should be

46*14190/85900584 ~ 0.76%

8.9% for a 3+ ball straight

85.7% for a 2+ ball straight

 

[shg]

I think people are generally sensitive about seeing patterns in numbers (or else there wouldn't be such fascination with the world's largest tax on innumeracy).

If there weren't four in a row, there might be a pattern like 1-3-5-7 or 1-4-7-10, or sequential primes, or your birthday, or today's DJIA, or ....

It seems to me as likely as not that four or the seven numbers would strike some resonant chord in most people.

 

[mirabeau]

I think people are generally sensitive about seeing patterns in numbers (or else there wouldn't be such fascination with the world's largest tax on innumeracy).

If there weren't four in a row, there might be a pattern like 1-3-5-7 or 1-4-7-10, or sequential primes, or your birthday, or today's DJIA, or ....

It seems to me as likely as not that four or the seven numbers would strike some resonant chord in most people.

Humans have always sought to make order out of chaos. For generations they've looked for patterns in prime numbers, in irrational numbers, the creation and operation of the universe - more recently manifested in the SETI searches. Maybe people just feel insecure otherwise.

Wasn't it the Academic Sceptics who argued that "Nothing is known, not even this", later disputed by the Pyrhonnists who held that even that statement implied one knew too much. Patterns indeed!

But please enlighten me. What is the world's largest tax on innumeracy? and why should anyone be fascinated by it?