Hi, i have spent ages trying to write formulas that will generate all combinations of the lottery. Thats 6 numbers and a single bonus number; all ranging from 1 - 49. I do not wish to use VB or macros, but will have to if no other solution is possible. Ideally the individual numbers will be in seperate cells (eg: A1=1,A2=2..etc). Can anyone help?
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Lottery Combinations
- Thread starter damainman
- Start date
Welcome to Mr. Excel's Board!
Try this: Add headers in Cell A1 through F1 saying "Ball 1" through "Ball 6". Put "Bonus" in G1.
In Cell A2 through G2 put the value 1.
In Cell A3 put the formula: =IF(B2=49,A2+1,A2)
Copy this formula accross to F3.
In cell G3 put the formula: =IF(G2=49,1,G2+1)
Now copy cells A3 through G3 down until all the values are 49.
Of course, that would take 678,223,072,849 rows. Good luck - since Excel doesn't go that far.
Try this: Add headers in Cell A1 through F1 saying "Ball 1" through "Ball 6". Put "Bonus" in G1.
In Cell A2 through G2 put the value 1.
In Cell A3 put the formula: =IF(B2=49,A2+1,A2)
Copy this formula accross to F3.
In cell G3 put the formula: =IF(G2=49,1,G2+1)
Now copy cells A3 through G3 down until all the values are 49.
Of course, that would take 678,223,072,849 rows. Good luck - since Excel doesn't go that far.
Upvote
0
HippoCampus
Board Regular
- Joined
- Sep 17, 2007
- Messages
- 195
Hi
I am afraid that I cannnot post an example because for some reason I do not have posting rights
To check our work syndicate numbers I have a worksheet with 7 columns A - I
Ball 1 - 6 (A-F)
Bonus Ball (G)
Bonus Calculation (H - Usuallly hidden)
Matches I
In G5 put
=ISNUMBER(MATCH(A5,$A$2:$F$2,0))+ISNUMBER(MATCH(B5,$A$2:$F$2,0))+ISNUMBER(MATCH(C5,$A$2:$F$2,0))+ISNUMBER(MATCH(D5,$A$2:$F$2,0))+ISNUMBER(MATCH(E5,$A$2:$F$2,0))+ISNUMBER(MATCH(F5,$A$2:$F$2,0))
In H5 put
=ISNUMBER(MATCH($G$2,A5:F5,0))
In I5 put
=IF(AND(G5=5,ISNUMBER(MATCH($G$2,A5:F5,0))),"5+Bonus","")
Highlight G - H and I and you will see a small black dot in the corner of the I cell, click hold and drag down for as many rows as you syndicate has numbers.
in A5 - F5 enter the first line of your syndicates numbers and then contine putting them in until you have put all of the syndicates number in.
When the lottery is drawn put the numbers into A2 - F2 and the bonus ball number into G2
You will then see that G5 and I5 will calculate how many winning lines that you have.
Hippo
I am afraid that I cannnot post an example because for some reason I do not have posting rights
To check our work syndicate numbers I have a worksheet with 7 columns A - I
Ball 1 - 6 (A-F)
Bonus Ball (G)
Bonus Calculation (H - Usuallly hidden)
Matches I
In G5 put
=ISNUMBER(MATCH(A5,$A$2:$F$2,0))+ISNUMBER(MATCH(B5,$A$2:$F$2,0))+ISNUMBER(MATCH(C5,$A$2:$F$2,0))+ISNUMBER(MATCH(D5,$A$2:$F$2,0))+ISNUMBER(MATCH(E5,$A$2:$F$2,0))+ISNUMBER(MATCH(F5,$A$2:$F$2,0))
In H5 put
=ISNUMBER(MATCH($G$2,A5:F5,0))
In I5 put
=IF(AND(G5=5,ISNUMBER(MATCH($G$2,A5:F5,0))),"5+Bonus","")
Highlight G - H and I and you will see a small black dot in the corner of the I cell, click hold and drag down for as many rows as you syndicate has numbers.
in A5 - F5 enter the first line of your syndicates numbers and then contine putting them in until you have put all of the syndicates number in.
When the lottery is drawn put the numbers into A2 - F2 and the bonus ball number into G2
You will then see that G5 and I5 will calculate how many winning lines that you have.
Hippo
Upvote
0
I have a correction to my original post.
Try this: Add headers in Cell A1 through F1 saying "Ball 1" through "Ball 6". Put "Bonus" in G1.
In Cell A2 through G2 put the value 1.
In Cell A3 put the formula:
Copy this formula across to F3.
In cell G3 put the formula:
Now copy cells A3 through G3 down until all the values are 49.
Good luck.
Try this: Add headers in Cell A1 through F1 saying "Ball 1" through "Ball 6". Put "Bonus" in G1.
In Cell A2 through G2 put the value 1.
In Cell A3 put the formula:
Code:
=IF(COUNT(A2:$G2)=COUNTIF(A2:$G2,49),1,IF(COUNT(B2:$G2)=COUNTIF(B2:$G2,49),A2+1,A2))In cell G3 put the formula:
Code:
=IF(G2=49,1,G2+1)Good luck.
Upvote
0
barry houdini
MrExcel MVP
- Joined
- Mar 23, 2005
- Messages
- 20,825
In a regular lottery no number can be chosen more than once in each draw so there are COMBIN(49,6)= 13,983,816 different combinations of 6 numbers that can be chosen. Adding a bonus ball will multiply those combinations by 43 giving 601,304,088.
Unless you have excel 2007 that's going to take more than 250 worksheets to list those, possibly a challenge, even for a VBA solution, certainly impractical to use formulas....
Unless you have excel 2007 that's going to take more than 250 worksheets to list those, possibly a challenge, even for a VBA solution, certainly impractical to use formulas....
Upvote
0
