Hello!

I've been looking at this all over the internet, and either I'm the first one that looks for this or I can't word it properly... I can't find anything.

I would like Excel to give me all the possible combinations of a 6/36 lottery, but only to win the 4 out of 6 numbers. There should be around 320 combinations.

For example these would be valid combinations:

1 2 3 4 5 6
1 2 3 7 8 9
1 2 4 10 11 12

These wouldn't be valid:

1 2 3 4 5 6

1 2 3 4 5 7

2 3 4 6 10 11

because "1 2 3 4" and "2 3 4 6" are appearing twice.

I hope someone will be kind enough to point me to the right direction. Please let me know if my explanation isn't clear.

Thanks a bunch!

Martin

"Martin-888" wrote:
I would like Excel to give me all the possible combinations
of a 6/36 lottery, but only to win the 4 out of 6 numbers.

I would like to help.

But first, let's agree to ignore those whose only comment is to pontificate
about how foolish playing the lottery is. Yes, yes, yes; just like all
gambling. But it presents some very interesting, if not fun, mathematical
problems. After all, it was the analysis of games of chance that gave rise
to probability theory in the first place.

So I am not judging your motivations. "Frankly, my dear, I don't give a
damn!" :-)

But I am curious, because this question has come up before.

So please explain the context of this question.

For example, is it a class assignment?

Or is it part of a lottery strategy? If so, can you explain the strategy,
or can you point to a (free) website that does?


"Martin-888" wrote:
There should be around 320 combinations.
For example these would be valid combinations:
1 2 3 4 5 6
1 2 3 7 8 9
1 2 4 10 11 12
These wouldn't be valid:
1 2 3 4 5 6
1 2 3 4 5 7
2 3 4 6 10 11
because "1 2 3 4" and "2 3 4 6" are appearing twice.

Based on this example, I think a better description of your objective is:
all combinations of 6 out of 36 numbers with a unique set of 4 numbers.

It has nothing to do with "winning" 4 out of 6. To me, that means:
matching 4 out of 6 compared to some drawing of 6 numbers. That's a very
different problem; an easier problem to solve, IMHO.

Anyway, what makes you think there are only "around 320" such combinations?

I believe the correct count is 2240.

At the risk of reinventing the wheel (I cannot find my comments in the
previous discussion), I implemented an algorithm that counts and generates
all of the qualified combinations by exhaustively generating all
COMBIN(36,6) combinations (1,947,792) and keeping track of the ones with
unique sets of 4 numbers. It only takes about 1 second on my computer.
(YMMV.)

I will share that implementation with you after you answer my questions
above.

In the meantime, I am still improving the implmentation so it is easier for
public consumption. I would also like to see if there is a better
algorithm. And I am still strugging to derive a computational method for
counting the number of combinations.

In any case, be advised that this cannot be done with Excel alone. Instead,
it requires a VBA subroutine (macro). Is that acceptable?

First of all, thank you for being interested in my question :)

It is part of a lottery strategy, but it does imply buying all the 320 combinations (which I'll explain why I think there is only 320 combinations later), therefore guaranteeing at least one win of the 4 out of 6 prize. Also I just want to point out that English isn't my native language, sometimes I run out of word to properly explain my thought :)

I'm not very good in math, but since there is 1 chance out of 320, it made me believe that there was many groups of 320 combinations. Having Excel taking out all of the matching combinations would answer that question.

Just to make sure that I'm properly understood, I want to clarify a point.

In my 4 out of 6 unique combinations, the 4 out of 6 has to be completely unique:

If you take 1,2,3,4,5,6 as my starting combination, the other combinations will not be able to have the following inside of them:

1,2,3,4
2,3,4,5
3,4,5,6
1,3,4,5
1,4,5,6
2,4,5,6
1,2,4,5
1,2,5,6
1,3,4,6
1,3,5,6
1,4,5,6

So for the script to work properly, the next logical combination would be 1,2,3,7,8,9 since 1,2,3,4,5,7 wouldn't be an acceptable one. Also, starting with the first combination being 2,3,4,5,6,7 then this creates a different list of combinations, with different exclusions. I hope this makes more sense?

VBA script is perfect, I didn't think that Excel alone would be able to create this either. Is this how you first understood my question or my clarification changes it? Also, your algorithm that creates all the combinations within 1 second is impressive, all the ones I found online takes nearly 1 hour to complete and I have a Core i7.

Please let me know if you need more info :)

Thanks!

[Hello Martin,

i think that right answer is 58 905 rows.

Try these macros. I suppose that you know about macros, so
that you can try these ones.

First takes about 10 mins and second taken about 2 minutes.

best regards

Hello!

Thank you for the VBA script, unfortunately that gives me 4 numbers combinations, which doesn't work for what I need; I need 6 numbers combination with a unique 4 numbers combinations inside of it. While this script would give me the first 4 numbers, it wouldn't detect a match in the rest of the combinations, when the extra 2 numbers are added. For example:

If the script gives me this:

1,2,3,4
1,2,3,5
1,2,3,6
1,2,3,7

I need to add the extra two digits, which will then create duplicate unique 4 numbers within the combination:

1,2,3,4,5,6
1,2,3,5,6,7

Here you have 1,2,3,5 & 1,2,3,6 & 1,2,5,6 & 1,3,5,6 & 2,3,5,6 that matches in both combinations.

"Martin-888" wrote:
It is part of a lottery strategy, but it does imply buying
all the 320 combinations [...], therefore guaranteeing at
least one win of the 4 out of 6 prize.

Sounds like a scam.

Please provide a URL (http://...) for the website that describes the
strategy or offers the product.

Also, please provide a URL (http://...) for the particular lottery.


"Martin-888" wrote:
I'm not very good in math, but since there is 1 chance
out of 320, it made me believe that there was many groups
of 320 combinations.

No. For example, there are 6525 ways (tickets) of matching exactly 4 out of
6 numbers drawn. That translates into about a 1 in 298.5 chance of matching
exactly 4, since there are 1,947,792 possible drawings (tickets) of 6 out of
36 numbers.

I still do not understand where 320 comes from. That is why I would like
the URLs that I requested above. They probably don't explain the math
directly. But they might have information that I can use to intuit the
computation.

For now, I stand by my finding that you would have to buy 2240 tickets in
order to be sure to match at least 4 of 6.

Of course, that makes sense to do only if the payout for matching exactly 4
is more than $2240, assuming $1 per ticket. (And of course, by "$", I mean
whatever your currency is.)

I suspect you will find that the payout is much smaller. Otherwise,
everyone would be doing this.

"If it sounds too good to be true, it usually is".


"Martin-888" wrote:
In my 4 out of 6 unique combinations, the 4 out of 6 has
to be completely unique
[....]
Is this how you first understood my question

Yes. You can download the file "uniq 4 from 6 of 36.xls" from
https://www.box.com/s/2a361ad6de78e8f4865e.

Columns A and B are somewhat configurable by changing A1, A2 and A3.
However, the table starting at A11 might need to be adjusted manually.

The macro that generates the results in columns D through I is hardwired for
the particular parameters that you mentioned, namely: drawing 6 of 36
numbers, and generating all combinations with unique sets of 4 numbers.

I hardwired the numbers in order to ensure the best performance.

The worksheet and macro might not be very self-explanatory. Let me know if
you have any questions or comments.

I wrote:
"Martin-888" wrote:
It is part of a lottery strategy, but it does imply buying
all the 320 combinations [...], therefore guaranteeing at
least one win of the 4 out of 6 prize.

Sounds like a scam.

Please provide a URL (http://...) for the website that describes
the strategy or offers the product.

Aha! I believe you are talking about what is called "lottery wheels" and
"lottery wheeling" in English.

But I believe you might have misunderstood some details. And quite
understandably so, especially if you are reading English descriptions, and
English is not your native language, as you indicated. I found it difficult
to understand some of the details, and English __is__ my native language
:-). I finally "got it" after reading several descriptions and examples.


I wrote:
For now, I stand by my finding that you would have to buy 2240
tickets in order to be sure to match at least 4 of 6.

I am even more confident now that I correctly "wheeled" the full set of 36
numbers to guarantee "a minimum 4-number match", as the "wheelers" put it.

I will be double-checking my algorithm later, just to be sure I don't have a
defect.

Nevertheless, I suspect that is not exactly what you wanted, at least not
according to one detail that you posted.


I wrote:
"Martin-888" wrote:
I'm not very good in math, but since there is 1 chance
out of 320, it made me believe that there was many groups
of 320 combinations.
[....]
I still do not understand where 320 comes from.

I believe I do, now. But I wonder if you really meant to write 1 chance in
389, 325, or 315, not 320.

There are two common flavors of lottery wheeling: full wheel and
abbreviated wheel. (There are other flavors, as well.)

With a full wheel, you typically choose a subset of the 36 numbers, say 18
numbers. Then we generate __all__ combinations of 6 of the __18__ numbers,
not 36, with unique sets of 4. That would be a total of 42 combinations in
this case.

With an abbreviated wheel, again you choose a subset of the 36 numbers, say
18 numbers. Then we generate a __subset__ of the combinations 6 of the
__18__ numbers with unique set of 4. That would be fewer than 42
combinations in this case. (See below for how we define the subset of
qualified combinations.)

The key difference is the condition under which the wheel "guarantees" a
minimum 4-number match.

With a full wheel, a minimum 4-number match is "guaranteed" as long as
__all_6__ of the drawn numbers (by the lottery) are in your subset of 18
numbers.

With an abbreviate wheel, a minimum 4-number match is "guaranteed" as long
as, for example, at least __4__ of the 6 drawn numbers are in your subset of
18 numbers.

At issue is the word "guaranteed". It is really a __conditional__
guarantee.

Given the condition for full wheels ("all 6 of the drawn numbers are in your
subset"), the probability that the condition is met is COMBIN(18,6) /
COMBIN(36,6) for a subset is 18 numbers.

Similarly, for abbreviated wheels, the probability that the condition ("at
least 4 of the drawn numbers are in your subset") is met is COMBIN(18,4) /
COMBIN(36/6).

This is where the "1 chance in 320" comes from: ostensibly, COMBIN(k,6) /
COMBIN(36,6) is 1/320 for some subset of k numbers.

However, I am unable to find any k for which the conditional probability is
exactly 1/320.

It would be 1/389 (1 chance in 389) for a full wheel with a subset of 15
numbers.

It would be 1/325 for an abbreviated wheel with a subset of 21 numbers and
the condition that at least 4 of the 6 drawn numbers are in the subset.

And it would be 1/315 for an abbreviated wheel with a subset of 17 numbers
and the condition that at least 5 of the 6 drawn numbers are in the subset.

Does any of that sound familiar -- closer to the facts in your circumstance?


I wrote:
Of course, that makes sense to do only if the payout
for matching exactly 4 is more than $2240, assuming $1
per ticket.

I neglected to also take into consideration other possible lesser matches
with some of the other combinations. That is too complicated to explain
further. I hope you can imagine what I mean.

PS: It might useful if I modify my algorithm to handle any full and
abbreviated wheel characteristics. Something for the future.

Errata.... I wrote:
Given the condition for full wheels ("all 6 of the drawn numbers are in
your subset"), the probability that the condition is met is COMBIN(18,6) /
COMBIN(36,6) for a subset is 18 numbers.

Similarly, for abbreviated wheels, the probability that the condition ("at
least 4 of the drawn numbers are in your subset") is met is COMBIN(18,4) /
COMBIN(36/6).

While I believe the full-wheel conditional probability is correct, I have my
doubts about the formula for the abbreviated-wheel conditional probability.
I need to give that more thought.

I believe what you are looking for is 'lotto wheeling' algorithms. If
you google that you'll find there's many samples of popular wheeling
combos for 6 number draws that will claim to result in 3/4/5/6 out of
<numbers drawn odds of winning *IF* the numbers drawn happen to be in
your wheel. There are also what is called 'abbreviated' wheeling combos
which do not include all possible combos of candidate numbers, but
these are based on popular combos with the same win ratio as full
wheels. In any case you need to provide the required 'candidate'
numbers for the chosen wheel. For example...

One popular (abbreviated) wheeling combo generates 42 tickets with a
5 out of 6 odds of winning based on using only 16 candidate numbers.
The full wheel would generate all possible combinations of those 16
candidate numbers.

...where the cost/return ratio of this could be prohibitive at best if
you did not choose the right 16 candidate numbers!

Lotteries are 'random' gaming that do not have any 'mystical' results
attached<IMO, and so there are no 'guaranteed' ways to win other than
playing a full wheel of every possible ticket combo for the numbers
used (ie: 6/39, 6/49, 7/49...)!!!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

"GS" wrote:
One popular (abbreviated) wheeling combo generates 42
tickets with a 5 out of 6 odds of winning based on using
only 16 candidate numbers.

Please post a pointer to this website. I would like to understand those
odds ("5 out of 6") better.

Or do you mean: 5 out of 6 odds of matching at least some number (4?), but
__only_if__ at least some number (4?) of the drawn numbers are in the subset
of 16 numbers?

In other words, the real probability of "winning" is 5/6 times some
conditional probability (TBD)?

Even so, I would be interested in looking at the website.

joeu2004 has brought this to us :
"GS" wrote:
One popular (abbreviated) wheeling combo generates 42
tickets with a 5 out of 6 odds of winning based on using
only 16 candidate numbers.

Please post a pointer to this website. I would like to understand those odds
("5 out of 6") better.

Or do you mean: 5 out of 6 odds of matching at least some number (4?), but
__only_if__ at least some number (4?) of the drawn numbers are in the subset
of 16 numbers?

In other words, the real probability of "winning" is 5/6 times some
conditional probability (TBD)?

Even so, I would be interested in looking at the website.

I don't have a link to any particular website per se. I just googled
"lottery wheeling" and got lots of hits!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

I knocked something up to get a feel, pretty crude and slow but it churned
out 1,947,792 combinations of 6 numbers of which 16,431 fitted your '4 out
of 6' criteria. IOW would need to buy 16k tickets. Before spending time
trying to figure out what I might have misunderstood or why the routine
might be wrong, please explain why you are say "There should be around 320
combinations."

Regards,
Peter T



"Martin-888" wrote in message
...

Hello!

I've been looking at this all over the internet, and either I'm the
first one that looks for this or I can't word it properly... I can't
find anything.

I would like Excel to give me all the possible combinations of a 6/36
lottery, but only to win the 4 out of 6 numbers. There should be around
320 combinations.

For example these would be valid combinations:

1 2 3 4 5 6
1 2 3 7 8 9
1 2 4 10 11 12

These wouldn't be valid:

1 2 3 4 5 6

1 2 3 4 5 7

2 3 4 6 10 11

because "1 2 3 4" and "2 3 4 6" are appearing twice.

I hope someone will be kind enough to point me to the right direction.
Please let me know if my explanation isn't clear.

Thanks a bunch!

Martin




--
Martin-888

"Peter T" wrote:
I knocked something up to get a feel, pretty crude
and slow but it churned out 1,947,792 combinations
of 6 numbers of which 16,431 fitted your '4 out of 6' criteria.

I would like to understand why you and I got very different results.

Please upload your results -- the 16,431 combinations -- to a file-sharing
website and post the "shared", "public" or "view-only" link (aka URL;
http://...) in a response here. The following is a list of some free
file-sharing websites; or use your own.

Box.Net: http://www.box.net/files
Windows Live Skydrive: http://skydrive.live.com
MediaFi http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
FileDropper: http://www.filedropper.com
RapidSha http://www.rapidshare.com

"joeu2004" wrote in message
...
"Peter T" wrote:
I knocked something up to get a feel, pretty crude
and slow but it churned out 1,947,792 combinations
of 6 numbers of which 16,431 fitted your '4 out of 6' criteria.

I would like to understand why you and I got very different results.

Please upload your results -- the 16,431 combinations -- to a file-sharing
website

16,431, after spotting a small error now only 16,430

It's small enough to post here, but watch for word wrap -

Option Explicit
Sub test6()
Dim i As Long, j As Long
Dim s As String
Dim arrCombs() As Long

Range("a:a").Clear

Combs4from6 arrCombs, 36

For i = 1 To UBound(arrCombs, 2)
s = arrCombs(1, i)
For j = 2 To 6
s = s & " " & arrCombs(j, i)
Next
Cells(i, 1) = s
Next

End Sub

Function Combs4from6(bigArr, mx As Long) As Long
Dim a1 As Long, a2 As Long, a3 As Long, a4 As Long, a5 As Long, a6 As Long
Dim i As Long, j As Long
Dim r As Long, x As Long

ReDim arr(1 To 6) As Long
ReDim bigArr(1 To 6, 1 To 1000) As Long

For a1 = mx - (mx - 6) + 1 To mx
arr(1) = arr(1) + 1
arr(2) = arr(1)
For a2 = a1 - 1 To mx
arr(2) = arr(2) + 1
arr(3) = arr(2)
For a3 = a2 To mx
arr(3) = arr(3) + 1
arr(4) = arr(3)
For a4 = a3 To mx
arr(4) = arr(4) + 1
arr(5) = arr(4)
For a5 = a4 To mx
arr(5) = arr(5) + 1
arr(6) = arr(5)
For a6 = a5 To mx
arr(6) = arr(6) + 1
r = r + 1
filterComb bigArr, arr, x
Next
Next
Next
Next
Next
Next

' the last one
For i = 1 To 6
arr(i) = mx - 6 + i
Next
filterComb bigArr, arr, x

ReDim Preserve bigArr(1 To 6, 1 To x)

Combs4from6 = x
End Function
Function filterComb(bigArr, arr() As Long, x As Long) As Boolean
Dim b As Boolean
Dim i As Long, j As Long, k As Long
Dim f As Long

On Error GoTo errH

If x < 1 Then
x = x + 1
For i = 1 To 6
bigArr(i, 1) = arr(i)
Next
filterComb = True
Else

For i = 1 To x
f = 0

For j = 1 To 6
For k = 1 To 6
If bigArr(j, x) = arr(k) Then
b = True
Exit For
End If
Next

If b Then
f = f + 1
b = False
If f = 4 Then
' already found 4 dups, no point to look for more
Exit Function
End If
End If

If j - f 2 Then
' can't be 4 dups in this array so skip to the next
Exit For
End If

Next
Next

x = x + 1
For i = 1 To 6
999 bigArr(i, x) = arr(i)
Next
filterComb = True
End If

Exit Function
errH:
If Err.Number = 9 And Erl = 999 Then
'need to resize the array
ReDim Preserve bigArr(1 To 6, 1 To UBound(bigArr, 2) + 1000)
Resume
End If

End Function

It works like this
- Combs4from6 makes all the 6-number combinations, each temporarily to a 6
number array.
- filterComb compares the array looking for 4 duplicate numbers in all
previously retained arrays. If 4 dups are not found the array of 6 is added
to the main array

Combs4from6 is I think highly efficient and well optimzed. filterComb is
'efficient' but the entire approach isn't. If filterComb is commented in
Combs4from6 1.9m combinations are produced in barely a tad. However the
filter approach means take a coffee or two. Probably a different approach
would speed things up considerably. In particular, look into only making
correct combinations (rather than all 1.9m) in the first place and avoid the
need to filter; it's one of those "'get your head round it' sort of things!

I have NOT checked results, so until otherwise confirmed do not assume this
all works correctly!

Regards,
Peter T

"Peter T" wrote:
"joeu2004" wrote in message
"Peter T" wrote:
I knocked something up to get a feel, pretty crude
and slow but it churned out 1,947,792 combinations
of 6 numbers of which 16,431 fitted your '4 out of 6' criteria.

I would like to understand why you and I got very different results.
[....]
16,431, after spotting a small error now only 16,430 It's small enough to
post here

Well, that implementation does not seem to work at all.

Of the 16,430 combinations, only 153 fit Martin's requirement, namely:
every quad subset of the 6 numbers in each combination is unique (there are
15 such quad subsets). That is, they do not appear together in any other
combination.

If you are intereted, see "peterT combo.xls" at
https://www.box.com/s/409aee79b7b3949970f1. See the explanation in the
worksheet "README FIRST".

For example, in the "data" worksheet, combination/row #12 shows:

A C:H I J:O
1 2 4 5 6 7 1 2 4 5 6 7 #1 1 2 3 4 5 6

Note that 1 2 4 5 in C:H appeared first in row #1, shown in J:O for
convenience. I believe that violates Martin's requirements.

There really is no need for me to try to identify the flaw(s) in your
implementation. I had already pointed to my implementation, which seems to
truly work (and it is m-u-c-h faster). It produces 2240 combinations with
unique quad subsets.

If you are interested, see "uniq 4 from 6 of 36.xls" at
https://www.box.com/s/2a361ad6de78e8f4865e.

"joeu2004" wrote in message
...

There really is no need for me to try to identify the flaw(s) in your
implementation. I had already pointed to my implementation, which seems
to truly work (and it is m-u-c-h faster). It produces 2240 combinations
with unique quad subsets.
If you are interested, see "uniq 4 from 6 of 36.xls" at
https://www.box.com/s/2a361ad6de78e8f4865e.

Yeah there was a silly typo in mine,
in filterComb change
If bigArr(j, x) = arr(k) Then
to
If bigArr(j, i) = arr(k) Then

and guess what - it also returns 2240 combinations !

With that change although probably now correct it's even slower, I'll look
at yours tomorrow.

That said, presumably the task is a one off so I suppose speed is not
critical.

Regards,
Peter T

"joeu2004" wrote in message
...

If you are intereted, see "peterT combo.xls" at
https://www.box.com/s/409aee79b7b3949970f1. See the explanation in the
worksheet "README FIRST".

I couldn't find a README FIRST, but no worries, as I've already posted there
was a single letter typo in my code, it now produces same results as yours
(and in same order).

There really is no need for me to try to identify the flaw(s) in your
implementation. I had already pointed to my implementation, which seems
to truly work (and it is m-u-c-h faster). It produces 2240 combinations
with unique quad subsets.

Just to recap, as I mentioned I knocked something up very quickly just to
get a feel, not even sure I understood the objective yet alone if mine was
correct. You asked me to post it which I did. You didn't appear to be
entirely confirdent about yours either at the time.

Anyway, indeed yours is indeed very much faster, and very elegant too.
Actally the main aspects of both out codes are very similar (albeit not at
first glance). However but my filter approach is, not wrong but, poorly
conceived. Maybe a fresh look would have brought that in line with yours
too.

regards,
Peter T

Hi Martin!

I understand what you're looking for. You want to generate all possible combinations of 4 unique numbers from a set of 6 numbers (1-36) without any repetition of the same combination. Here's how you can do it in Excel:

1. Create a column for each of the 6 numbers (let's say A, B, C, D, E, F).
2. In the next column, use the formula =COMBIN(6,4) to calculate the total number of possible combinations of 4 numbers from 6.
3. In the next column, use the formula =RAND() to generate a random number for each row.
4. Sort the table by the random number column (ascending or descending, it doesn't matter).
5. In the next column, use the formula =A2&B2&C2&D2 to concatenate the 4 numbers in each row into a single cell.
6. In the next column, use the formula =IF(COUNTIF($E$2:E2,E2)=1,"Valid","Duplicate") to check if the combination in each row is valid (unique) or a duplicate.
7. Filter the table by the "Valid" column to show only the valid combinations.

That's it! You should now have a list of all possible combinations of 4 unique numbers from 6 without any repetition of the same combination. Let me know if you have any questions or if anything is unclear.