Find lottery 5_50 3rd price combinations from 12 numbers

[Kishan]

Using Excel 2000

Hi,

Lottery EuroMillions 5_50 for example playing 12 numbers from 1 to 12 there are =COMBIN(12,5)=792 combination (if draw result of 5 numbers is outcome from 1 to 12 numbers there is one ticket with 1st price)

1st price covered by 5 numbers (forget about 2 stars)
2nd price covered by 4 numbers (forget about 2 stars)

I am curious to know how many numbers of combinations I have to play out of 792 which can cover 2nd price (I mean as 792 cover 5 numbers, there must be fewer combinations which must be covered 4 numbers may be I am wrong)

Is it possible VBA SOLUTION?

Thank you in advance

Regards,
Kishan

 

[StephenCrump]

I am curious to know how many numbers of combinations I have to play out of 792 which can cover 2nd price (I mean as 792 cover 5 numbers, there must be fewer combinations which must be covered 4 numbers may be I am wrong)

I am not clear what your question is?

Is it possible VBA SOLUTION?

Or what you want VBA to do?

Here are the basic probabilities:

	A	B
1	N	50
2	k	5
3	S	5
4
5	Numbers
6	Correct	Probability
7	5	0.00%
8	4	0.01%
9	3	0.47%
10	2	6.70%
11	1	35.16%
12	0	57.66%
13		100.00%
Sheet1

Cell Formulas
Range	Formula
B7:B12	B7	=IFERROR(COMBIN(S,A7)*COMBIN(N-S,k-A7),0)/COMBIN(N,k)
B13	B13	=SUM(B7:B12)

Named Ranges
Name	Refers To	Cells
k	=Sheet1!$B$2	B7:B12
N	=Sheet1!$B$1	B7:B12
S	=Sheet1!$B$3	B7:B12

At the other end of the scale, the only way to guarantee first prize is to use all the numbers:

N	50
k	5
S	50
Numbers
Correct	Probability
5	100.00%
4	0.00%
3	0.00%
2	0.00%
1	0.00%
0	0.00%
	100.00%

Playing all combinations of 12 chosen numbers:

N	50
k	5
S	12
Numbers
Correct	Probability
5	0.04%
4	0.89%
3	7.30%
2	26.28%
1	41.81%
0	23.69%
	100.00%

I am not sure what you mean by "cover" 2nd prize? It's just a question of increasing the probability of getting a prize by playing more numbers.

Also, be careful how you interpret these numbers. The table shows that with all combinations of any 12 numbers, you have a 0.89% chance of a 2nd prize. This actually means a 0.89% chance of one or more 2nd prizes, but not a first prize.

The 0.04% chance of a first prize actually means a 0.04% chance of a first prize plus 35 2nd prizes.

 

[Kishan]

I am not clear what your question is?

StephenCrump, Thank you for the answer

As you explain (Playing all combinations of 12 chosen numbers) N=50, k=5 and S=12 this mean will be generate total =COMBIN(12,5)=792 combinations.

Out of 792... we have a 0.89% chance of a 2nd prize. This actually means a 0.89% chance of one or more 2nd prizes, but not a first prize....I am agreeing with your explanation.

I am not clear what your question is?

My question if we play all combination of 12 chosen numbers =792 and if the outcome result of 5 numbers is drawn within our 12 chosen numbers...we have for sure 1st price is it correct?

But I want to play out of 792 only those combinations that give me 2nd or 3rd price are it is possible to find such less combination (if draw outcome is from any 792 combinations which are chosen from 12 selected numbers)

I have created a simple example may that help to my viewpoint.

For example we pick 7 numbers from 1 to 7, that generate =COMBIN(7,5)=21 combinations.
As shown in the range C5:G25...Now play just 3 combinations are in range L5:P7...you will see if any result outcome out of 21 there are 2nd price for sure. (And the 1st price only within 3 had chosen played combinations).

		=COMBIN(7,5)=21
1​	2​	3​	4​	5​	6​	7​
	Combi	n1	n2	n3	n4	n5		Match		Combi	n1	n2	n3	n4	n5
	1	1	2	3	4	5		4		1	1	2	3	4	7
	2	1	2	3	4	6		4		2	1	2	3	5	6
	3	1	2	3	4	7		5		3	2	4	5	6	7
	4	1	2	3	5	6		5
	5	1	2	3	5	7		4
	6	1	2	3	6	7		4
	7	1	2	4	5	6		4
	8	1	2	4	5	7		4
	9	1	2	4	6	7		4
	10	1	2	5	6	7		4
	11	1	3	4	5	6		4
	12	1	3	4	5	7		4
	13	1	3	4	6	7		4
	14	1	3	5	6	7		4
	15	1	4	5	6	7		4
	16	2	3	4	5	6		4
	17	2	3	4	5	7		4
	18	2	3	4	6	7		4
	19	2	3	5	6	7		4
	20	2	4	5	6	7		5
	21	3	4	5	6	7		4

Is it possible VBA that can choose? If we play 12 numbers =COMBIN(12,5)=792 combinations. And bring out only those cover 2nd price as shown in example wit 7 numbers?


My Regards,
Kishan

 

[StephenCrump]

You can use formulae to get these results:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Combi	n1	n2	n3	n4	n5		Match		Combi	n1	n2	n3	n4	n5
2	1	1	2	3	4	5		4		1	1	2	3	4	7
3	2	1	2	3	4	6		4		2	1	2	3	5	6
4	3	1	2	3	4	7		5		3	2	4	5	6	7
5	4	1	2	3	5	6		5
6	5	1	2	3	5	7		4
7	6	1	2	3	6	7		4
8	7	1	2	4	5	6		4
9	8	1	2	4	5	7		4
10	9	1	2	4	6	7		4
11	10	1	2	5	6	7		4
12	11	1	3	4	5	6		4
13	12	1	3	4	5	7		4
14	13	1	3	4	6	7		4
15	14	1	3	5	6	7		4
16	15	1	4	5	6	7		4
17	16	2	3	4	5	6		4
18	17	2	3	4	5	7		4
19	18	2	3	4	6	7		4
20	19	2	3	5	6	7		4
21	20	2	4	5	6	7		5
22	21	3	4	5	6	7		4
Sheet1

Cell Formulas
Range	Formula
H2:H22	H2	=MAX(MMULT(COUNTIF(B2:F2,K$2:O$4),{1;1;1;1;1}))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[StephenCrump]

The 0.04% chance of a first prize actually means a 0.04% chance of a first prize plus 35 2nd prizes.

Sorry, this was an assumption, based on how I think the Australian Lotto works - that a ticket winning first prize will necessarily also win multiple second prizes.

I don't know that this is correct.

 

[Kishan]

Sorry, this was an assumption, based on how I think the Australian Lotto works - that a ticket winning first prize will necessarily also win multiple second prizes.

I don't know that this is correct.

StephenCrump, you are correct as you described (that a ticket winning first prize will necessarily also win multiple second prizes.) we are close please see the example below..matches in column J with each played combina.tion.

L5:P5 played 1st combination has 1-match with 1st price and 10-matches with 2nd price.
L6:P6 played 2nd combination has 1-match with 1st price and 6-matches with 2nd price.
L7:P7 played 3rd combination has 1-match with 1st price and 2-matches with 2nd price.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1			=COMBIN(7,5)=21
2	1​	2​	3​	4​	5​	6​	7​
3
4		Combi	n1	n2	n3	n4	n5		Match	With Played 3 Combi	Combi	n1	n2	n3	n4	n5
5		1	1	2	3	4	5		4	1	1	1	2	3	4	7
6		2	1	2	3	4	6		4	1	2	1	2	3	5	6
7		3	1	2	3	4	7		5	1	3	2	4	5	6	7
8		4	1	2	3	5	6		5	2
9		5	1	2	3	5	7		4	1
10		6	1	2	3	6	7		4	1
11		7	1	2	4	5	6		4	2
12		8	1	2	4	5	7		4	1
13		9	1	2	4	6	7		4	1
14		10	1	2	5	6	7		4	2
15		11	1	3	4	5	6		4	2
16		12	1	3	4	5	7		4	1
17		13	1	3	4	6	7		4	1
18		14	1	3	5	6	7		4	2
19		15	1	4	5	6	7		4	3
20		16	2	3	4	5	6		4	2
21		17	2	3	4	5	7		4	1
22		18	2	3	4	6	7		4	1
23		19	2	3	5	6	7		4	2
24		20	2	4	5	6	7		5	3
25		21	3	4	5	6	7		4	3
26
27

You will see that only playing only 3 combinations has covered 1st and 2nd price (3 combinations has cover 1st price) and (18 combinations has cover 2nd price) total 21 covered by 3 combinations.

This I have checked one by one manually and find 3 that cover 2nd price for sure within 21 these are just 7 numbers... but 12 numbers has 792 combinations. Is it possible VBA solution that could find few within 792 only that cover 1st and 2nd price?

My Regards,
Kishan

 

[Kishan]

You can use formulae to get these results:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Combi	n1	n2	n3	n4	n5		Match		Combi	n1	n2	n3	n4	n5
2	1	1	2	3	4	5		4		1	1	2	3	4	7
3	2	1	2	3	4	6		4		2	1	2	3	5	6
4	3	1	2	3	4	7		5		3	2	4	5	6	7
5	4	1	2	3	5	6		5
6	5	1	2	3	5	7		4
7	6	1	2	3	6	7		4
8	7	1	2	4	5	6		4
9	8	1	2	4	5	7		4
10	9	1	2	4	6	7		4
11	10	1	2	5	6	7		4
12	11	1	3	4	5	6		4
13	12	1	3	4	5	7		4
14	13	1	3	4	6	7		4
15	14	1	3	5	6	7		4
16	15	1	4	5	6	7		4
17	16	2	3	4	5	6		4
18	17	2	3	4	5	7		4
19	18	2	3	4	6	7		4
20	19	2	3	5	6	7		4
21	20	2	4	5	6	7		5
22	21	3	4	5	6	7		4
Sheet1

Cell Formulas
Range	Formula
H2:H22	H2	=MAX(MMULT(COUNTIF(B2:F2,K$2:O$4),{1;1;1;1;1}))
Press CTRL+SHIFT+ENTER to enter array formulas.

Hello StephenCrump, Hope all is well

A part of post#6, please can you help me...If I change data in post#4 as shown below can you provide the (Multi Match) formula which give the result as shown in column H (Match).

Combi	n1	n2	n3	n4	n5		Match		Combi	n1	n2	n3	n4	n5
1	X	2	X	2	1		5		1	X	2	X	2	1
2	1	2	2	1	2		3		2	1	1	X	X	X
3	2	2	1	X	1		2		3	1	1	1	1	2
4	X	1	X	2	1		4
5	2	1	2	X	2		2
6	1	2	2	1	1		2
7	1	1	2	2	2		3
8	1	1	1	1	1		4
9	1	1	X	X	X		5
10	X	2	2	2	X		3
11	1	2	1	1	1		3
12	1	1	X	2	X		4
13	X	2	X	2	X		4
14	X	X	2	2	2		2
15	X	X	X	X	1		3
16	1	1	2	1	X		3
17	2	1	2	1	X		2
18	1	1	1	1	2		5
19	1	1	1	2	2		4
20	X	1	2	1	X		2
21	2	1	2	1	2		3

My Regards,
Kishan

 

[Kishan]

Please Help

 

[Kishan]

You can use formulae to get these results:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Combi	n1	n2	n3	n4	n5		Match		Combi	n1	n2	n3	n4	n5
2	1	1	2	3	4	5		4		1	1	2	3	4	7
3	2	1	2	3	4	6		4		2	1	2	3	5	6
4	3	1	2	3	4	7		5		3	2	4	5	6	7
5	4	1	2	3	5	6		5
6	5	1	2	3	5	7		4
7	6	1	2	3	6	7		4
8	7	1	2	4	5	6		4
9	8	1	2	4	5	7		4
10	9	1	2	4	6	7		4
11	10	1	2	5	6	7		4
12	11	1	3	4	5	6		4
13	12	1	3	4	5	7		4
14	13	1	3	4	6	7		4
15	14	1	3	5	6	7		4
16	15	1	4	5	6	7		4
17	16	2	3	4	5	6		4
18	17	2	3	4	5	7		4
19	18	2	3	4	6	7		4
20	19	2	3	5	6	7		4
21	20	2	4	5	6	7		5
22	21	3	4	5	6	7		4
Sheet1

Cell Formulas
Range	Formula
H2:H22	H2	=MAX(MMULT(COUNTIF(B2:F2,K$2:O$4),{1;1;1;1;1}))
Press CTRL+SHIFT+ENTER to enter array formulas.

Can this

You can use formulae to get these results:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Combi	n1	n2	n3	n4	n5		Match		Combi	n1	n2	n3	n4	n5
2	1	1	2	3	4	5		4		1	1	2	3	4	7
3	2	1	2	3	4	6		4		2	1	2	3	5	6
4	3	1	2	3	4	7		5		3	2	4	5	6	7
5	4	1	2	3	5	6		5
6	5	1	2	3	5	7		4
7	6	1	2	3	6	7		4
8	7	1	2	4	5	6		4
9	8	1	2	4	5	7		4
10	9	1	2	4	6	7		4
11	10	1	2	5	6	7		4
12	11	1	3	4	5	6		4
13	12	1	3	4	5	7		4
14	13	1	3	4	6	7		4
15	14	1	3	5	6	7		4
16	15	1	4	5	6	7		4
17	16	2	3	4	5	6		4
18	17	2	3	4	5	7		4
19	18	2	3	4	6	7		4
20	19	2	3	5	6	7		4
21	20	2	4	5	6	7		5
22	21	3	4	5	6	7		4
Sheet1

Cell Formulas
Range	Formula
H2:H22	H2	=MAX(MMULT(COUNTIF(B2:F2,K$2:O$4),{1;1;1;1;1}))
Press CTRL+SHIFT+ENTER to enter array formulas.

Can formula H2:H22 could be converted in VBA? because I want to check 10000+ result

Regards,
Kishan

 

[Kishan]

Any suggestions please