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.

 

[damainman]

wow this that definately got a lot of attention. i know a lot of you will see this as a pointless exercise, but it is a challenge! weaver thanks alot! simon, i suppose it would be interesting to view it all on 1 sheet - just would make it harder to analyse i think.

i have excel 2003 on this machine so will get another machine to do that last solution for me later!

by the way, the reason i want all this is so i can probe it with some queries and get rid of combinations i feel are unlikely to come up (though not impossible). i want to reduce the 14mill figure by as much as i can and see what odds i can reduce that fugure to.

for example, i would get rid of:
1.2.3.4.5.6
1.21.22.23.24.25 (and other any single number with a serial 5)
1.2.3.41.42.43 (unlikely to get 2 sets of serial 3 numbers right?)
1.5.10.11.12.13...

...i think you get my drift.

i want to see by eliminating as many unlikely combinations - what i can reduce the odds to. even after reducing the unlikely (not impossible) combinations; i will probably be left over with a sizable odds figure, but atleast a lot smaller i think depending on how far i decide to go.

anyhoo, its just a curiosity and a challenge. who knows, maybe i might be able to get a large enough syndicate together if the jackpot justified/new designed odds justified it.

 

[Weaver]

Lol, just because you think there's some sort of pattern, doesn't make the combination any less likely than another.

Unless you suspect there's collusion behind the scenes to make sure randomness is percieved rather than actual....

You should look at discounting any combo with nos. > 31 in, since most people use family birthdays!

 

[damainman]

i see your point weaver. however, i was going on historical lottery numbers and how rarely a sequence of numbers occur.

i havent got excel 2007 were i am now. tried the code anyway, it produced a single sheet of numbers and 200+ blank sheets! anyway, im gonna try it on 2007 when i get home later.

 

[Weaver]

if ubound(xArr)=5 then

In this line the 5 is 1 less than the number of 'balls' so if you want to do a quicker test, change this. Set to 3 should produce 4 sheets of data (don't worry about the #N/A)

 

[damainman]

thats works! however, only fills 1 sheet and creates loads of blank sheets? maybe its a excel 2003 issue and i should wait until i get home to try on 2007?

 

[Trebor76]

Hi Weaver,

Sorry, my bad. My mind instantly took me to our (Australian) lotto which is 6 from 45.

I'm glad I was at least able to introduce you to the COMBIN function.

Cheers,

Robert

 

[Weaver]

thats works! however, only fills 1 sheet and creates loads of blank sheets? maybe its a excel 2003 issue and i should wait until i get home to try on 2007?

Dunno, I tested it on 2002 and that created exactly the number of records I was ecpecting, over 4 sheets. Can you start off afresh with a new workbook and paste the code into that, see if it's still a problem?

Hi Weaver,

Sorry, my bad. My mind instantly took me to our (Australian) lotto which is 6 from 45.

I'm glad I was at least able to introduce you to the COMBIN function.

Cheers,

Robert

No problem - COMBIN() was great for proving the code worked. Yes I know it's all a bit pointless, but it's not often you really happen across a good excuse for recursion!

 

[tusharm]

No, that is not correct. *Every* combination is equally likely. Without exception. Unless, of course, the lottery is rigged. Which is what makes listing every combination an exercise without a purpose.

wow this that definately got a lot of attention. i know a lot of you will see this as a pointless exercise, but it is a challenge! weaver thanks alot! simon, i suppose it would be interesting to view it all on 1 sheet - just would make it harder to analyse i think.

i have excel 2003 on this machine so will get another machine to do that last solution for me later!

by the way, the reason i want all this is so i can probe it with some queries and get rid of combinations i feel are unlikely to come up (though not impossible). i want to reduce the 14mill figure by as much as i can and see what odds i can reduce that fugure to.

for example, i would get rid of:
1.2.3.4.5.6
1.21.22.23.24.25 (and other any single number with a serial 5)
1.2.3.41.42.43 (unlikely to get 2 sets of serial 3 numbers right?)
1.5.10.11.12.13...

...i think you get my drift.

i want to see by eliminating as many unlikely combinations - what i can reduce the odds to. even after reducing the unlikely (not impossible) combinations; i will probably be left over with a sizable odds figure, but atleast a lot smaller i think depending on how far i decide to go.

anyhoo, its just a curiosity and a challenge. who knows, maybe i might be able to get a large enough syndicate together if the jackpot justified/new designed odds justified it.

 

[ukmikeb]

for example, i would get rid of:
1.2.3.4.5.6
1.21.22.23.24.25 (and other any single number with a serial 5)
1.2.3.41.42.43 (unlikely to get 2 sets of serial 3 numbers right?)
1.5.10.11.12.13...

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

 

[West Man]

Why rule out 1.2.3.4.5.6? Compute the probability and provide another combination that is more likely?

Or just play combinations that have hit in the past. After all, no other winning combinations have ever hit.

Or eliminate those that have hit in the past. After all it is extremely unlikely to have a repeat.

Or realize that there is nothing you can do to increase your odds.