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

 

[xenou]

I don't believe you can copy them all at once. You have to do one column at a time. Randbetween cannot be guaranteed to not select the same number. I think the point is that in lotteries it never matters if a number is drawn first, second, third ... etc. since they are always "sorted" into ascending order for the purposes of reporting the result - so a number drawn is a number drawn. If you think you can predict lottery results this way you need to take STAT 101 at your local community college ASAP.

 

[xenou]

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.

 

[mirabeau]

to generate say 100,000 sets of lottery-type numbers reasonably quickly, say <3 secs depending on computer, you can try this

Sub generatelottery()

Const l& = 1        'lower value
Const u& = 49       'upper value
Const n& = 6        'number of numbers per lottery
Dim a() As String, s As String, b() As Boolean
Dim rws&, i&, j&, x&, k&
rws = 10 ^ 5        'number of sets of lottery numbers
ReDim a(1 To rws, 1 To 1)

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
    For j = l To u
        If b(j) Then s = s & "|" & j
    Next j
a(i, 1) = Right(s, Len(s) - 1)
Next i
Range("A1").Resize(rws) = a

End Sub

although if you want to analyze the possibilities of shifting lottery odds in your favor, I doubt that Excel's random generation would be the same as that of the lottery.

Incidentally, if you do want to analyze sets of 100,000 or 1,000,000 or so, you may find the Pivot Table rather slow.

 

[K_McIntosh]

Thanks to all who Replied ... xenou ... your suggestion to take STAT 101 at my local community college ASAP is not lost on me ... however, this exercise is still a good excuse to learn more about Excel ...

On that note, would someone here mind pointing me to a practical Code (as specifically suggested above in the "Code:" windows by xenou and mirabeau) implementation tutorial on this forum ... something not too involved that would allow me to move forward in a practical sense would be best for me for starters ... are we Record(ing a) New Macro ... or ?

Thanks, K_McIntosh

 

[xenou]

Fair enough.
There have been some recent discussions on learning excel in our general discussion forum:
http://www.mrexcel.com/forum/genera...656339-how-did-you-guys-learn-everything.html
http://www.mrexcel.com/forum/genera...s/655117-visual-basic-applications-books.html

Hiker95 has a comprehensive list of online resources in the above post (several are geared to beginners). Also try googling "Excel VBA tutorial". Good luck! I learned the hard way - reading books, trying things, doing things the wrong way, and slowly figuring out better ways. Some years ago I also found certain members to be very useful to me - learning from their posts and working out how their solutions went until I understood how they worked.

 

[K_McIntosh]

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

I've got xenou's Code, called by '=RandomLotteryNumber(1,49,6)', stored as (a Microsoft Visual Basic) 'Module1', under 'VBA Project (filename1.xls)' > 'Modules' in a given Workbook (filename1.xls), however, it is not visible under 'Tools' > 'Macro' > 'Macros' in said Workbook ... is the Code running by virtue of entering the calling formula nevertheless?

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

 

[shg]

xenou's code is implemented as a user-defined function, so yes, you just enter the formula in a cell. It doesn't appear under Tools > Macros because (a) it's a Function, not a Sub, and (b) it requires arguments.

 

[xenou]

My function does not appear in the macros dialog because it takes parameters (the values for min, max, digits). Subs or Functions that takes parameters are "invisible" in this regard. Mirabeau's code is probably failing you because it returns 100000 rows which is more than can fit into an xls file (limited to 65536 rows).

This is a bad hack (apologies to mirabeau) to shorten the results (a more robust fix might put the data in more than one column or on more than one sheet):

[COLOR="Navy"]Sub[/COLOR] generatelottery()

[COLOR="Navy"]Const[/COLOR] l& = 1        [COLOR="SeaGreen"]'lower value[/COLOR]
[COLOR="Navy"]Const[/COLOR] u& = 49       [COLOR="SeaGreen"]'upper value[/COLOR]
[COLOR="Navy"]Const[/COLOR] n& = 6        [COLOR="SeaGreen"]'number of numbers per lottery[/COLOR]
[COLOR="Navy"]Dim[/COLOR] a() [COLOR="Navy"]As[/COLOR] String, s [COLOR="Navy"]As[/COLOR] String, b() [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Boolean[/COLOR]
[COLOR="Navy"]Dim[/COLOR] rws&, i&, j&, x&, k&
rws = 10 ^ 5        [COLOR="SeaGreen"]'number of sets of lottery numbers[/COLOR]
[COLOR="Navy"]ReDim[/COLOR] a(1 [COLOR="Navy"]To[/COLOR] rws, 1 [COLOR="Navy"]To[/COLOR] 1)

Randomize
[COLOR="Navy"]For[/COLOR] i = 1 [COLOR="Navy"]To[/COLOR] rws
    [COLOR="Navy"]ReDim[/COLOR] b(l [COLOR="Navy"]To[/COLOR] u): k = 0: s = ""
    [COLOR="Navy"]Do[/COLOR]
        x = Int(Rnd * u) + l
        [COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] b(x) [COLOR="Navy"]Then[/COLOR] k = k + 1: b(x) = True
    [COLOR="Navy"]Loop[/COLOR] Until k = n
    [COLOR="Navy"]For[/COLOR] j = l [COLOR="Navy"]To[/COLOR] u
        [COLOR="Navy"]If[/COLOR] b(j) [COLOR="Navy"]Then[/COLOR] s = s & "|" & j
    [COLOR="Navy"]Next[/COLOR] j
a(i, 1) = Right(s, Len(s) - 1)
[COLOR="Navy"]Next[/COLOR] i

[COLOR="Navy"]If[/COLOR] UBound(a, 1) > 65536 [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]For[/COLOR] x = 1 [COLOR="Navy"]To[/COLOR] 65536
        Cells(x, 1).Value = a(x, 1)
    [COLOR="Navy"]Next[/COLOR] x
    MsgBox "Results exceed 65536 rows!"
[COLOR="Navy"]Else[/COLOR]
    Range("A1").Resize(rws) = a
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]


[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
[COLOR="Navy"]Function[/COLOR] RandomLotteryNumber(ByVal minValue [COLOR="Navy"]As[/COLOR] Long, maxValue [COLOR="Navy"]As[/COLOR] Long, digits [COLOR="Navy"]As[/COLOR] Long) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]

my code can be used by calling it from a cell in the spreadsheet.
=RandomLotteryNumber(1,49,7)
Which would supply a lottery number of 7 digits, using the numbers 1 to 49. You can also enter this formula by using the formula wizard on the formulas tab (I have forgotten where it is in Excel 2003 - maybe it was a button right to the left of where you'd type the formulas on the formula bar). When you select the formula category choose UDF (User-Defined) and you should see it.

Here, by the way is an alternate version you could use:

[COLOR="Navy"]Function[/COLOR] RandomLotteryNumber_Array(ByVal minValue [COLOR="Navy"]As[/COLOR] Long, maxValue [COLOR="Navy"]As[/COLOR] Long, digits [COLOR="Navy"]As[/COLOR] Long) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Variant[/COLOR]
[COLOR="Navy"]Dim[/COLOR] dic [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR]  [COLOR="SeaGreen"]'//Scripting.Dictionary[/COLOR]
[COLOR="Navy"]Dim[/COLOR] x [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] a, i

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

In this version, you select the cells A1 to G1 (a seven cell range corresponding to the seven digits of the lottery number, and enter the formula into cell A1:
=RandomLotteryNumber_Array(1,49,7)
But you must press Control-Shift-Enter, not just enter, to array enter the formula. Now the seven cells will hold the seven digits.

 

[chucklesl066]

All sounds overly complicated to me.

I use the following

[SIZE=2]Sub euromillions_number_generator()

[/SIZE][SIZE=2][COLOR=#3cb371][SIZE=2][COLOR=#3cb371]'

'globally declare variables

'

[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]Dim iloop As Integer

Dim array1() As Variant

Dim array2() As Variant

Dim already_picked() As Boolean

Dim already_picked2() As Boolean

Dim random_number As Long

Dim myRange As Range

Dim low As Long

Dim high As Long

Dim lowx As Long

Dim highx As Long

Dim lshigh As Double

Dim i As Long

Dim j As Long

Dim nogoes As Double

Dim zinput As Double

Dim aloops As Long

Dim loops As Long

Dim lloop As Long

Dim lloop2 As Long

Dim str1 As Long

Dim str2 As Long

[/SIZE][SIZE=2][COLOR=#3cb371][SIZE=2][COLOR=#3cb371]'

'turn off screen updating

'

[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]Application.ScreenUpdating = False

[/SIZE][SIZE=2][COLOR=#3cb371][SIZE=2][COLOR=#3cb371]'

'make sure random number generator is truly random by using system clock as seed

'

[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]Randomize Timer

[/SIZE][SIZE=2][COLOR=#3cb371][SIZE=2][COLOR=#3cb371]'

'get a random number of dummy loops to perform

'

[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]lowx = 1

highx = 250000

loops = Int((highx - lowx + 1) * Rnd() + lowx)

[/SIZE][SIZE=2][COLOR=#3cb371][SIZE=2][COLOR=#3cb371]'

'get number of lines

'

[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]zinput = InputBox("How many tickets to purchase?")

[/SIZE][SIZE=2][COLOR=#3cb371][SIZE=2][COLOR=#3cb371]'

'initialise range needed, 1-50 for main numbers and 1-11 for lucky stars

'

[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]lshigh = 11

low = 1

high = 50

[/SIZE][SIZE=2][COLOR=#3cb371][SIZE=2][COLOR=#3cb371]'

'initialise loop and truly randomize things

'

[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]For aloops = 1 To loops

random_number = Int((high - low + 1) * Rnd() + low)

Next

For nogoes = 1 To zinput

[/SIZE][SIZE=2][COLOR=#3cb371][SIZE=2][COLOR=#3cb371]'

'the real thing - get five main numbers

'

[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]ReDim array1(1 To 5)

ReDim already_picked(1 To high)

For iloop = 1 To 5

    Do

        random_number = Int((high - low + 1) * Rnd() + low)

[/SIZE][SIZE=2][COLOR=#3cb371][SIZE=2][COLOR=#3cb371]'

'if number already picked, loop back and try again

'

[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]    Loop Until Not (already_picked(random_number))

    array1(iloop) = random_number

    already_picked(random_number) = True

Next iloop

[/SIZE][SIZE=2][COLOR=#3cb371][SIZE=2][COLOR=#3cb371]'

'get two lucky stars

'

[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]ReDim array2(1 To 2)

ReDim already_picked2(1 To lshigh)

For iloop = 1 To 2

    Do

        random_number = Int((lshigh - low + 1) * Rnd() + low)

[/SIZE][SIZE=2][COLOR=#3cb371][SIZE=2][COLOR=#3cb371]'

'if number already picked, loop back and try again

'

[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]    Loop Until Not (already_picked2(random_number))

    array2(iloop) = random_number

    already_picked2(random_number) = True

Next iloop

[/SIZE][SIZE=2][COLOR=#3cb371][SIZE=2][COLOR=#3cb371]'

'sort both arrays so that numbers are generated in ascending order

'

[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]For lloop = 1 To UBound(array1)

    For lloop2 = lloop To UBound(array1)

        If (array1(lloop2)) < (array1(lloop)) Then

            str1 = array1(lloop)

            str2 = array1(lloop2)

            array1(lloop) = str2

            array1(lloop2) = str1

        End If

    Next lloop2

Next lloop

For lloop = 1 To UBound(array2)

    For lloop2 = lloop To UBound(array2)

        If (array2(lloop2)) < (array2(lloop)) Then

            str1 = array2(lloop)

            str2 = array2(lloop2)

            array2(lloop) = str2

            array2(lloop2) = str1

        End If

    Next lloop2

Next lloop

[/SIZE][SIZE=2][COLOR=#3cb371][SIZE=2][COLOR=#3cb371]'

'set cell range for output, A1 to A5 for main numbers, A7 to A8 for lucky stars

'

[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]Set myRange = Range("A1:A5")

a = 1

For Each myCell In myRange

myCell.Value = array1(a)

a = a + 1

Next

Set myRange = Range("A7:A8")

a = 1

For Each myCell In myRange

myCell.Value = array2(a)

a = a + 1

Next

[/SIZE][SIZE=2][COLOR=#3cb371][SIZE=2][COLOR=#3cb371]'

'move output to the right to allow for next set of numbers

'

[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]Range("A1:A8").Select

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Next nogoes

Range("A1:A8").Select

Selection.Delete Shift:=xlToLeft

Range("AA100").Select

[/SIZE][SIZE=2][COLOR=#3cb371][SIZE=2][COLOR=#3cb371]'

'turn on screen updating

'

[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]Application.ScreenUpdating = True

End Sub
[/SIZE]