all lottery combinations 1 - 49

[damainman]

i need excel to output all lottery combinations. the numbers are from 1-49 from which 6 numbers are drawn. i do realise the number of combinations are 13983816. i want the 6 numbers drawn to be in different cells across 6 different columns. so the 1st 6 numbers are in a1, b1, c1, d1, e1, f1. obviously excel 2003 has 65536 rows and so the formula/vb code should carry on onto a new worksheet or ideally a new workbook. its ideal in a new workbook so not to end up with a single huge 10gig file, but on several worksheets in 1 workbook is fine.

i have been trying to do this myself for a while now with no success. any help would be great. from what i understand; this is a bit of a challenge i know.

 

[tusharm]

To rule out a particular combination you have to show that the probability of that combination is less than that of some other combination. As West Man asked, can you do that? From the laws of probability, I know you cannot! When drawing N numbers independently from a pool of M numbers *every* combination of N numbers is equally likely.

In any case, either one believes there is a science underlying this analysis or one doesn't. It should be obvious where my beliefs lie. {grin} Consequently, this discussion is well past the point where I can contribute anything useful. So, I will stop monitoring it for further posts.

All the best to those who will find meaning in COMBIN(M,N) patterns of numbers.

Hi

Yes, you could be moving along the right lines here -

Definitely rule out series of 4, 5 and 6.

All those could be ignored with appropriate changes to the code.

Good luck with your project.

Mike

 

[Weaver]

this discussion is well past the point where I can contribute anything useful.

To be fair, I think it started at that point!

Having said that, it bothers me to think you can exclude certain combos. Having said that, if you want all the jackpot to yourself, choose at least some numbers from above 30 range, since I believe a lot of people use family birthdays for their selections.

Otherwise, if we're saying combos like 1,2,3,4,5,6 will never come out simply because they don't 'look' random, wouldn't this require some background manipulation?

BTW, if anyone's at all interested, (and you probably shouldn't be...) I've converted this to run in Access - takes about 20 minutes and of course all the data is in one (heck of a large) table.

 

[damainman]

wow a lot of posts since i last checked. i agree obviously with all those stating that each and every number is "statistically" as likely as any other number to be drawn in the lotto. however, in "reality" most will agree that the likely hood of a random set of numbers as opposed to a serial of 4, 5 or 6 is more likely "statistically". this can also be derived from historically drawn numbers also. thus if eliminated from all probabilities; the odds are drastically reduced. i would in addition take out past numbers among other number patterns.

end of the day, its a bit of fun and curiosity. my profession is a data analyst and thought id play with the numbers and see what i can conclude.

weaver: your welcome to email me that ms access idea!

supadupacushty@hotmail.com

 

[sobat]

if the numbers are not sequential (instead of 1-49) but the random numbers such as 1, 3, 5, 6, 7, 8, 9 11, 13, 16, 19? please code

 

[West Man]

You have said

"however, in "reality" most will agree that the likely hood of a random set of numbers as opposed to a serial of 4, 5 or 6 is more likely "statistically". this can also be derived from historically drawn numbers also."

So you would remove these because historically they have not appeared

i would in addition take out past numbers among other number patterns

And you would remove these because historically they have appeared If you remove sequences that have not appeared and ones that have appeared, it seems that nothing will be left.

 

[WatfordKev]

I've got a version that saves every time a new sheet is added and 2003 seems to fall over once the file size hits 100mb. Running in 2007 it's still going strong at 173 mb and currently 8 sheets!

****, I wish I hadn't read your post!

I hope damainman sees you alright for a big drink if the numbers come up!

 

[pjmorris]

Just thought I'd add to damainman's comment about a sequence 4, 5, 6 etc being unlikely because it doesn't look random. If the premise were that they need to be drawn in order, ie the 4 before the 5 before the 6 then the statistics definitely change since the probability of number n+1 is dependent on the previous number drawn. This would make the calculation way beyond anything I want to consider. The key point is that the lottery numbers are displayed in sequence (because we can check our cards more easily) but are drawn in a random order and hence there is no link between any two balls.

To perhaps explain this in a different way. If all the balls in the lottery were labelled with Chinese script (or any other sequence of shapes we may care to invent) since most of us don't understand the chinese script we would have little surprise at any combination - it is only our familiarity with the characters (or numbers) that leads to our surprise when they are shown sequentially; maths doesn't have such sentimental ideas.

Regards

 

[Yuki Adams]

Can I have a list of all lotto combination for 1-49

 

[raja_167]

Ran in 2007 and it filled 13 sheets, with 352,328 lines in sheet 14 (took about 90 minutes, 324mb file...!)

13 * 1,048,576 + 352,328 = 13,983,816

So if your maths was right to begin with, I'd say that's job done

Sub allLottery()
    Dim x As Long
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
    x = doTheLott("", 1, 1)
    With Application
        .StatusBar = False
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
    ActiveWorkbook.Save
End Sub

Function doTheLott(ByVal xStr As String, r As Long, a As Integer) As Long
    Dim xArr As Variant, i As Integer
    xArr = Split(Trim(xStr), " ")
    If UBound(xArr) = 5 Then
        Cells(r, 1).Resize(1, 6).Value = xArr
        r = r + 1
        If r > Rows.Count Then
            Sheets.Add
            ActiveWorkbook.Save
            r = 1
        End If
    Else
        For i = a To 49
            j = Format(i, "00")
            If InStr(xStr, j) = 0 Then
                r = doTheLott(xStr & j & " ", r, i)
            End If
        Next
    End If
    doTheLott = r
End Function

Mind you, I wouldn't try running it on 2003 again...

Hi,

Please help on this, How to insert below conditions on above mentioned code

Generate only those six digits, if below conditions is full-filled
At least one number from P#1 16,20,25,34,45 & Two numbers from P#2 5,6,8,15,18,49

For Example

1,3,5,16,36,49 Display in Excel File – From P#1 at least One number is available & P#2 Two number available
2,7,9,30,35,46 Shouldn’t display in Excel file – From P#1 at least One number is not available & P#2 Two numbers is not available
5,6,21,26,33,44 Shouldn’t display in Excel File – From P#1 at least One number is not available & P#2 Two numbers is available
15,20,22,33,36,47 Shouldn’t display in Excel file – From P#1 at least One number is available & P#2 Two numbers is not available
5,7,9,20,23,44 Shouldn’t display in Excel file – From P#1 at least One number is available & P#2 less than Two numbers is available
5,6,8,20,23,44 Shouldn’t display in Excel file – From P#1 at least One number is available & P#2 More than Two numbers is available
5,6,16,20,33,44 Display in Excel File – From P#1 at least One number is available (More than 1 allowed) & P#2 Two numbers is available

Thanks in advance !!

Regards
Raj

 

[trader10518]

Ran in 2007 and it filled 13 sheets, with 352,328 lines in sheet 14 (took about 90 minutes, 324mb file...!)

13 * 1,048,576 + 352,328 = 13,983,816

So if your maths was right to begin with, I'd say that's job done

Sub allLottery()
    Dim x As Long
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
    x = doTheLott("", 1, 1)
    With Application
        .StatusBar = False
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
    ActiveWorkbook.Save
End Sub

Function doTheLott(ByVal xStr As String, r As Long, a As Integer) As Long
    Dim xArr As Variant, i As Integer
    xArr = Split(Trim(xStr), " ")
    If UBound(xArr) = 5 Then
        Cells(r, 1).Resize(1, 6).Value = xArr
        r = r + 1
        If r > Rows.Count Then
            Sheets.Add
            ActiveWorkbook.Save
            r = 1
        End If
    Else
        For i = a To 49
            j = Format(i, "00")
            If InStr(xStr, j) = 0 Then
                r = doTheLott(xStr & j & " ", r, i)
            End If
        Next
    End If
    doTheLott = r
End Function

Mind you, I wouldn't try running it on 2003 again...

Could anyone suggest please what should be changed in this code in order to get all possible 4 digit outcomes out of the total 20 let say. Change values of n & r basically. Thanks.