Need help figuring out pattern

[michaelsmith559]

I'm trying to figure out patterns for someone who is playing the lottery. They want me to show the low/high & odd/even combinations broken down into four groups: low/odd, low/even, high/odd, high/even. I have most of it done but can't figure out what I'm missing. The total combinations should be 12103014 and I have 11186884. I need to find the missing patterns. Here is what I have done so far:

Lotto Odds&Prob.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Low/Odd	Low/Even	High/Odd	High/Even	Total
2	Items	18	17	17	18	70
3	Chosen
4	5	8568	6188	6188	8568
5	4	3060	2380	2380	3060
6	3	816	680	680	816
7	2	153	136	136	153
8	1	18	17	17	18
9
10
11		5					8568	1	1	1	8568
12			5				1	6188	1	1	6188
13				5			1	1	6188	1	6188
14					5		1	1	1	8568	8568
15		4	1				3060	17	1	1	52020
16		4		1			3060	1	17	1	52020
17		4			1		3060	1	1	18	55080
18		1	4				18	2380	1	1	42840
19			4	1			1	2380	17	1	40460
20			4		1		1	2380	1	18	42840
21		1		4			18	1	2380	1	42840
22			1	4			1	17	2380	1	40460
23				4	1		1	1	2380	18	42840
24				1	4		1	1	17	3060	52020
25			1		4		1	17	1	3060	52020
26		1			4		18	1	1	3060	55080
27		3	2				816	136	1	1	110976
28		3		2			816	1	136	1	110976
29		3			2		816	1	1	153	124848
30		2	3				153	680	1	1	104040
31			3	2			1	680	136	1	92480
32			3		2		1	680	1	153	104040
33		2		3			153	1	680	1	104040
34			2	3			1	136	680	1	92480
35				3	2		1	1	680	153	104040
36				2	3		1	1	136	816	110976
37			2		3		1	136	1	816	110976
38		2			3		153	1	1	816	124848
39		3	1	1			816	17	17	1	235824
40		3	1		1		816	17	1	18	249696
41		3		1	1		816	1	17	18	249696
42		1	3	1			18	680	17	1	208080
43		1	3		1		18	680	1	18	220320
44			3	1	1		1	680	17	18	208080
45			1	3	1		1	17	680	18	208080
46		1		3	1		18	1	680	18	220320
47			1	3	1		1	17	680	18	208080
48			1	1	3		1	17	17	816	235824
49		1		1	3		18	1	17	816	249696
50		1	1		3		18	17	1	816	249696
51		2	3				153	680	1	1	104040
52		2		3			153	1	680	1	104040
53		2			3		153	1	1	816	124848
54		3	2				816	136	1	1	110976
55			2	3			1	136	680	1	92480
56			2		3		1	136	1	816	110976
57				2	3		1	1	136	816	110976
58			3	2			1	680	136	1	92480
59		3		2			816	1	136	1	110976
60				3	2		1	1	680	153	104040
61			3		2		1	680	1	153	104040
62		3			2		816	1	1	153	124848
63		2	2	1			153	136	17	1	353736
64		2	2		1		153	136	1	18	374544
65		2		2	1		153	1	136	18	374544
66		2	1	2			153	17	136	1	353736
67		1	2	2			18	136	136	1	332928
68		1	2		2		18	136	1	153	374544
69		2	1	1	1		153	17	17	18	795906
70		1	2	1	1		18	136	17	18	749088
71		1	1	2	1		18	17	136	18	749088
72		1	1	1	2		18	17	17	153	795906	11186884	12103014
Sheet3

Cell Formulas
Range	Formula
F2	F2	=SUM(B2:E2)
B4:E8	B4	=COMBIN(B$2,$A4)
G69:J72,G11:J62	G11	=COMBIN(B$2,B11)
K11:K72	K11	=G11*H11*I11*J11
G63:J63	G63	=COMBIN(B2,B63)
G64:J64	G64	=COMBIN(B2,B64)
G65:J65	G65	=COMBIN(B2,B65)
G66:J66	G66	=COMBIN(B2,B66)
G67:J67	G67	=COMBIN(B2,B67)
G68:J68	G68	=COMBIN(B2,B68)
L72	L72	=SUM(K11:K72)

 

[ebea]

How do you conclude, that the result should be: 12103014 ?
This result you get, from =COMBIN(F$2;$A4)

 

[michaelsmith559]

How do you conclude, that the result should be: 12103014 ?
This result you get, from =COMBIN(F$2;$A4)

The game they are doing is choosing 5 main numbers from 70.
That is 12103014 combinations.
So in the above post, five numbers total must be chosen. You could choose 5 from just one group or a few across several groups so that the total of numbers chosen is 5.

 

[ebea]

I still cant's see, how you conclude that in your set of numbers, the result should be 12103014 ? As you write, the possible combinations, are this number, as a formula =COMBIN(F$2;$A4) will show.
But your set of numbers, differs from that possible combinations. And your explanation in #3 do not make it more clear to me, the logic behind.

 

[michaelsmith559]

I still cant's see, how you conclude that in your set of numbers, the result should be 12103014 ? As you write, the possible combinations, are this number, as a formula =COMBIN(F$2;$A4) will show.
But your set of numbers, differs from that possible combinations. And your explanation in #3 do not make it more clear to me, the logic behind.

Sorry, I will try to make it clearer.
They have to choose 5 numbers from a total of 70. You can use the combin function to calculate the combinations which returns 12103014. They broke the numbers down into 4 different ways of combining them. You can only have a total of 5 numbers but they come from the 4 groups. So for example, in B11 they chose 5 numbers from the low/odd group which give a 8568 combinations. Or they could choose 1 number from low/odd, 1 number from low/high, and 3 numbers from high/even which would give 249696 combinations (Cell K50). Maybe this helps explain it better? If not, I'll try again.

 

[michaelsmith559]

So I figured out I could just do the permutations for the arrangements and remove duplicates to get the patterns I needed. For example, I took 1112 ran a macro for permutations taking 4 at a time and then used the remove duplicates function. Now, I'll see if all the patterns I have equal the number of combinations. Thanks.

 

[michaelsmith559]

After figuring out the patterns using permutations and deleting the duplicates, I still do not have the 12103014 combinations. Did I remove duplicates I didn't need to or am I just missing something else entirely? My reasoning for the 12103014 is that it's still 70 numbers in total and your still choosing 5 at a time. Unless I am missing a pattern for the way they are broken down I'm not sure why the total combinations do not sum to 12103014. Any suggestions? Thanks.

Lotto Odds&Prob.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1		Low/Odd	Low/Even	High/Odd	High/Even	Total
2	Items	18	17	17	18	70
3	Chosen
4	5	8568	6188	6188	8568
5	4	3060	2380	2380	3060
6	3	816	680	680	816
7	2	153	136	136	153
8	1	18	17	17	18
9
10		0	0	0	5	1	1	1	8568	8568
11		0	0	5	0	1	1	6188	1	6188
12		0	5	0	0	1	6188	1	1	6188
13		5	0	0	0	8568	1	1	1	8568
14		0	0	4	1	1	1	2380	18	42840
15		0	0	1	4	1	1	17	3060	52020
16		0	4	0	1	1	2380	1	18	42840
17		0	4	1	0	1	2380	17	1	40460
18		0	1	0	4	1	17	1	3060	52020
19		0	1	4	0	1	17	2380	1	40460
20		4	0	0	1	3060	1	1	18	55080
21		4	0	1	0	3060	1	17	1	52020
22		4	1	0	0	3060	17	1	1	52020
23		1	0	0	4	18	1	1	3060	55080
24		1	0	4	0	18	1	2380	1	42840
25		1	4	0	0	18	2380	1	1	42840
26		0	0	2	3	1	1	136	816	110976
27		0	0	3	2	1	1	680	153	104040
28		0	2	0	3	1	136	1	816	110976
29		0	2	3	0	1	136	680	1	92480
30		0	3	0	2	1	680	1	153	104040
31		0	3	2	0	1	680	136	1	92480
32		2	0	0	3	153	1	1	816	124848
33		2	0	3	0	153	1	680	1	104040
34		2	3	0	0	153	680	1	1	104040
35		3	0	0	2	816	1	1	153	124848
36		3	0	2	0	816	1	136	1	110976
37		3	2	0	0	816	136	1	1	110976
38		1	1	0	3	18	17	1	816	249696
39		1	1	3	0	18	17	680	1	208080
40		1	0	1	3	18	1	17	816	249696
41		1	0	3	1	18	1	680	18	220320
42		1	3	1	0	18	680	17	1	208080
43		1	3	0	1	18	680	1	18	220320
44		0	1	1	3	1	17	17	816	235824
45		0	1	3	1	1	17	680	18	208080
46		0	3	1	1	1	680	17	18	208080
47		3	1	1	0	816	17	17	1	235824
48		3	1	0	1	816	17	1	18	249696
49		3	0	1	1	816	1	17	18	249696
50		1	1	1	2	18	17	17	153	795906
51		1	1	2	1	18	17	136	18	749088
52		1	2	1	1	18	136	17	18	749088
53		2	1	1	1	153	17	17	18	795906	7728132
MM Number Patterns

Cell Formulas
Range	Formula
F2	F2	=SUM(B2:E2)
B4:E8	B4	=COMBIN(B$2,$A4)
F10:I53	F10	=COMBIN(B$2,B10)
J10:J53	J10	=F10*G10*H10*I10
K53	K53	=SUM(J10:J53)

 

[michaelsmith559]

I found the missing pattern. Here is the sheet finished:

Lotto Odds&Prob.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1		Low/Odd	Low/Even	High/Odd	High/Even	Total				Combinations	Probability	Occur in 100	Occur in 100	Odds	Ways to Miss	Miss Probability	Miss in 100	Miss in 100		Number Patterns	Count
2	Items	18	17	17	18	70														0, 0, 0, 5	56
3	Chosen																			0, 0, 5, 0
4	5	8568	6188	6188	8568															0, 5, 0, 0
5	4	3060	2380	2380	3060															5, 0, 0, 0
6	3	816	680	680	816															0, 0, 1, 4
7	2	153	136	136	153															0, 0, 4, 1
8	1	18	17	17	18															0, 1, 0, 4
9																				0, 1, 4, 0
10		0	0	0	5	1	1	1	8568	8568	0.00070792	0	0	1 : 1413	12094446	0.999292077	99.93	99 to 100		0, 4, 0, 1
11		0	0	5	0	1	1	6188	1	6188	0.00051128	0	0	1 : 1956	12096826	0.999488722	99.95	99 to 100		0, 4, 1, 0
12		0	5	0	0	1	6188	1	1	6188	0.00051128	0	0	1 : 1956	12096826	0.999488722	99.95	99 to 100		1, 0, 0, 4
13		5	0	0	0	8568	1	1	1	8568	0.00070792	0	0	1 : 1413	12094446	0.999292077	99.93	99 to 100		1, 0, 4, 0
14		0	0	4	1	1	1	2380	18	42840	0.00353961	0	0	1 : 283	12060174	0.996460386	99.65	99 to 100		1, 4, 0, 0
15		0	0	1	4	1	1	17	3060	52020	0.0042981	0	0	1 : 233	12050994	0.995701897	99.57	99 to 100		4, 0, 0, 1
16		0	4	0	1	1	2380	1	18	42840	0.00353961	0	0	1 : 283	12060174	0.996460386	99.65	99 to 100		4, 0, 1, 0
17		0	4	1	0	1	2380	17	1	40460	0.00334297	0	0	1 : 299	12062554	0.996657031	99.67	99 to 100		4, 1, 0, 0
18		0	1	0	4	1	17	1	3060	52020	0.0042981	0	0	1 : 233	12050994	0.995701897	99.57	99 to 100		0, 0, 2, 3
19		0	1	4	0	1	17	2380	1	40460	0.00334297	0	0	1 : 299	12062554	0.996657031	99.67	99 to 100		0, 0, 3, 2
20		4	0	0	1	3060	1	1	18	55080	0.00455093	0	0	1 : 220	12047934	0.995449067	99.54	99 to 100		0, 2, 0, 3
21		4	0	1	0	3060	1	17	1	52020	0.0042981	0	0	1 : 233	12050994	0.995701897	99.57	99 to 100		0, 2, 3, 0
22		4	1	0	0	3060	17	1	1	52020	0.0042981	0	0	1 : 233	12050994	0.995701897	99.57	99 to 100		0, 3, 0, 2
23		1	0	0	4	18	1	1	3060	55080	0.00455093	0	0	1 : 220	12047934	0.995449067	99.54	99 to 100		0, 3, 2, 0
24		1	0	4	0	18	1	2380	1	42840	0.00353961	0	0	1 : 283	12060174	0.996460386	99.65	99 to 100		2, 0, 0, 3
25		1	4	0	0	18	2380	1	1	42840	0.00353961	0	0	1 : 283	12060174	0.996460386	99.65	99 to 100		2, 0, 3, 0
26		0	0	2	3	1	1	136	816	110976	0.00916929	1	0 to 1	1 : 109	11992038	0.990830714	99.08	99		2, 3, 0, 0
27		0	0	3	2	1	1	680	153	104040	0.00859621	1	0 to 1	1 : 116	11998974	0.991403794	99.14	99		3, 0, 0, 2
28		0	2	0	3	1	136	1	816	110976	0.00916929	1	0 to 1	1 : 109	11992038	0.990830714	99.08	99		3, 0, 2, 0
29		0	2	3	0	1	136	680	1	92480	0.00764107	1	0 to 1	1 : 131	12010534	0.992358928	99.24	99		3, 2, 0, 0
30		0	3	0	2	1	680	1	153	104040	0.00859621	1	0 to 1	1 : 116	11998974	0.991403794	99.14	99		0, 1, 1, 3
31		0	3	2	0	1	680	136	1	92480	0.00764107	1	0 to 1	1 : 131	12010534	0.992358928	99.24	99		0, 1, 3, 1
32		2	0	0	3	153	1	1	816	124848	0.01031545	1	1	1 : 97	11978166	0.989684553	98.97	98 to 99		0, 3, 1, 1
33		2	0	3	0	153	1	680	1	104040	0.00859621	1	0 to 1	1 : 116	11998974	0.991403794	99.14	99		1, 0, 1, 3
34		2	3	0	0	153	680	1	1	104040	0.00859621	1	0 to 1	1 : 116	11998974	0.991403794	99.14	99		1, 0, 3, 1
35		3	0	0	2	816	1	1	153	124848	0.01031545	1	1	1 : 97	11978166	0.989684553	98.97	98 to 99		1, 1, 0, 3
36		3	0	2	0	816	1	136	1	110976	0.00916929	1	0 to 1	1 : 109	11992038	0.990830714	99.08	99		1, 1, 3, 0
37		3	2	0	0	816	136	1	1	110976	0.00916929	1	0 to 1	1 : 109	11992038	0.990830714	99.08	99		1, 3, 0, 1
38		1	1	0	3	18	17	1	816	249696	0.02063089	2	2	1 : 48	11853318	0.979369106	97.94	97 to 98		1, 3, 1, 0
39		1	1	3	0	18	17	680	1	208080	0.01719241	2	1 to 2	1 : 58	11894934	0.982807588	98.28	98		3, 0, 1, 1
40		1	0	1	3	18	1	17	816	249696	0.02063089	2	2	1 : 48	11853318	0.979369106	97.94	97 to 98		3, 1, 0, 1
41		1	0	3	1	18	1	680	18	220320	0.01820373	2	1 to 2	1 : 55	11882694	0.98179627	98.18	98		3, 1, 1, 0
42		1	3	1	0	18	680	17	1	208080	0.01719241	2	1 to 2	1 : 58	11894934	0.982807588	98.28	98		0, 1, 2, 2
43		1	3	0	1	18	680	1	18	220320	0.01820373	2	1 to 2	1 : 55	11882694	0.98179627	98.18	98		0, 2, 1, 2
44		0	1	1	3	1	17	17	816	235824	0.01948473	2	1 to 2	1 : 51	11867190	0.980515267	98.05	98		0, 2, 2, 1
45		0	1	3	1	1	17	680	18	208080	0.01719241	2	1 to 2	1 : 58	11894934	0.982807588	98.28	98		1, 0, 2, 2
46		0	3	1	1	1	680	17	18	208080	0.01719241	2	1 to 2	1 : 58	11894934	0.982807588	98.28	98		1, 2, 0, 2
47		3	1	1	0	816	17	17	1	235824	0.01948473	2	1 to 2	1 : 51	11867190	0.980515267	98.05	98		1, 2, 2, 0
48		3	1	0	1	816	17	1	18	249696	0.02063089	2	2	1 : 48	11853318	0.979369106	97.94	97 to 98		2, 0, 1, 2
49		3	0	1	1	816	1	17	18	249696	0.02063089	2	2	1 : 48	11853318	0.979369106	97.94	97 to 98		2, 0, 2, 1
50		1	1	1	2	18	17	17	153	795906	0.06576097	7	6 to 7	1 : 15	11307108	0.934239025	93.42	93		2, 1, 0, 2
51		1	1	2	1	18	17	136	18	749088	0.06189268	6	6	1 : 16	11353926	0.938107318	93.81	93 to 94		2, 1, 2, 0
52		1	2	1	1	18	136	17	18	749088	0.06189268	6	6	1 : 16	11353926	0.938107318	93.81	93 to 94		2, 2, 0, 1
53		2	1	1	1	153	17	17	18	795906	0.06576097	7	6 to 7	1 : 15	11307108	0.934239025	93.42	93		2, 2, 1, 0
54		0	1	2	2	1	17	136	153	353736	0.0292271	3	2 to 3	1 : 34	11749278	0.9707729	97.08	97		1, 1, 1, 2
55		0	2	1	2	1	136	17	153	353736	0.0292271	3	2 to 3	1 : 34	11749278	0.9707729	97.08	97		1, 1, 2, 1
56		0	2	2	1	1	136	136	18	332928	0.02750786	3	2 to 3	1 : 36	11770086	0.972492141	97.25	97		1, 2, 1, 1
57		1	0	2	2	18	1	136	153	374544	0.03094634	3	3	1 : 32	11728470	0.969053659	96.91	96 to 97		2, 1, 1, 1
58		1	2	0	2	18	136	1	153	374544	0.03094634	3	3	1 : 32	11728470	0.969053659	96.91	96 to 97
59		1	2	2	0	18	136	136	1	332928	0.02750786	3	2 to 3	1 : 36	11770086	0.972492141	97.25	97
60		2	0	1	2	153	1	17	153	397953	0.03288049	3	3	1 : 30	11705061	0.967119513	96.71	96 to 97
61		2	0	2	1	153	1	136	18	374544	0.03094634	3	3	1 : 32	11728470	0.969053659	96.91	96 to 97
62		2	1	0	2	153	17	1	153	397953	0.03288049	3	3	1 : 30	11705061	0.967119513	96.71	96 to 97
63		2	1	2	0	153	17	136	1	353736	0.0292271	3	2 to 3	1 : 34	11749278	0.9707729	97.08	97
64		2	2	0	1	153	136	1	18	374544	0.03094634	3	3	1 : 32	11728470	0.969053659	96.91	96 to 97
65		2	2	1	0	153	136	17	1	353736	0.0292271	3	2 to 3	1 : 34	11749278	0.9707729	97.08	97
66										12103014
MM Number Patterns

Cell Formulas
Range	Formula
F2	F2	=SUM(B2:E2)
U2	U2	=COUNTA(T:T)-1
B4:E8	B4	=COMBIN(B$2,$A4)
F10:I65	F10	=COMBIN(B$2,B10)
K10:K65,P10:P65	K10	=J10/$J$66
L10:L65	L10	=ROUND(K10*100,0)
M10:M65,R10:R65	M10	=IF(INT(ROUND(K10*100,2))=ROUND(ROUND(K10*100,2),0),INT(ROUND(K10*100,2)),INT(ROUND(K10*100,2))&" to "&ROUND(ROUND(K10*100,2),0))
N10:N65	N10	=1&" : "&(ROUND(1/K10,0))
O10:O65	O10	=$J$66-J10
Q10:Q65	Q10	=ROUND(P10*100,2)
J10:J65	J10	=F10*G10*H10*I10
J66	J66	=SUM(J10:J65)

 

[michaelsmith559]

Here are a couple more sheets that go with the above trying to find combinations:

Lotto Odds&Prob.xlsm
	G	H	I	J	K
1	Low/High	Combinations	Probability	Occur in 100	Ways to Miss
2	0 Low / 5 High	324632	0.02682241	2 to 3	11778382
3	1 Low / 4 High	1832600	0.151416829	15	10270414
4	2 Low / 3 High	3894275	0.321760761	32	8208739
5	3 Low / 2 High	3894275	0.321760761	32	8208739
6	4 Low / 1 High	1832600	0.151416829	15	10270414
7	5 Low / 0 High	324632	0.02682241	2 to 3	11778382
8	Total Comb	12103014
9	Low: 1 to 35
10	High: 36 to 70
11
12
13	Odd/Even	Combinations	Probability	Occur in 100	Ways to Miss
14	0 Odd / 5 Even	324632	0.02682241	2 to 3	11778382
15	1 Odd / 4 Even	1832600	0.151416829	15	10270414
16	2 Odd / 3 Even	3894275	0.321760761	32	8208739
17	3 Odd / 2 Even	3894275	0.321760761	32	8208739
18	4 Odd / 1 Even	1832600	0.151416829	15	10270414
19	5 Odd / 0 Even	324632	0.02682241	2 to 3	11778382
20	Total Comb	12103014
21	35 Odd
22	35 Even
PB MM C4L JB

Cell Formulas
Range	Formula
I2:I7	I2	=H2/$H$8
J14:J19,J2:J7	J2	=IF(INT(ROUND(I2*100,2))=ROUND(ROUND(I2*100,2),0),INT(ROUND(I2*100,2)),INT(ROUND(I2*100,2))&" to "&ROUND(ROUND(I2*100,2),0))
K2:K7	K2	=$H$8-H2
H2,H14	H2	=COMBIN(35,0)*COMBIN(35,5)
H3,H15	H3	=COMBIN(35,1)*COMBIN(35,4)
H4,H16	H4	=COMBIN(35,2)*COMBIN(35,3)
H5,H17	H5	=COMBIN(35,3)*COMBIN(35,2)
H6,H18	H6	=COMBIN(35,4)*COMBIN(35,1)
H7,H19	H7	=COMBIN(35,5)*COMBIN(35,0)
H8,H20	H8	=SUM(H2:H7)
I14:I19	I14	=H14/$H$20
K14:K19	K14	=$H$20-H14

On the sheet below you can change the cells in blue to different numbers to see how the probability and odds change if you have the winning numbers in the amount you are leaving.

Lotto Odds&Prob.xlsm
	A	B
1		MegaMillions
2	Choose Main	5
3	Main Balls	70
4	Bonus	25
5	Main Comb	12103014
6	Bonus Comb	25
7	Odds	1 : 302575350
8	Probability	0.000000003304961888006
9
10
11	Change Main	67
12	Change Bonus	23
13	Main Comb	9657648
14	Bonus Comb	23
15	Odds	1 : 222125904
16	Probability	0.000000004501951289751
Sheet13

Cell Formulas
Range	Formula
B5	B5	=COMBIN(B3,B2)
B6,B14	B6	=COMBIN(B4,1)
B7,B15	B7	=1&" : "&(B5*B6)
B8,B16	B8	=1/((RIGHT(B7,LEN(B7)-4))+0)
B13	B13	=COMBIN(B11,B2)