Chances

[elviajero]

I am looking for the formula required to calculate the chances of matching numbers in the following scenario.

4 Separate Draws
Each draw has numbers 1 - 49
Player can choose 1 number between 1 - 49

Example draw:
45, 34, 14, 18, 32, 10
27, 12, 24, 45, 31, 03
08, 24, 23, 13, 17, 45
05, 45, 30, 08, 06, 13

How do I calculate the chances that a player matches their chosen number Once, Twice, Three or Four times from the 24 drawn numbers.

 

[StephenCrump]

Welcome to the Forum!

Excel 2010

	A	B
1	No	Prob.
2	0	59.3%
3	1	33.1%
4	2	6.9%
5	3	0.6%
6	4	0.0%

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

B2: =p^A2*(1-p)^(4-A2)*COMBIN(4,A2) copy down
p: = 1 - COMBIN(48,6)/COMBIN(49,6) = 12% approx

 

[StephenCrump]

I'm overly complicating ... p obviously is just 6/49.

 

[elviajero]

Welcome to the Forum!

Excel 2010

	A	B
1	No	Prob.
2	0	59.3%
3	1	33.1%
4	2	6.9%
5	3	0.6%
6	4	0.0%

<tbody>
</tbody>

Sheet2​

B2: =p^A2*(1-p)^(4-A2)*COMBIN(4,A2) copy down
p: = 1 - COMBIN(48,6)/COMBIN(49,6) = 12% approx

Many thanks Stephen.

Perfect!

How would I adapt this if the number of draws was 1, 2, 3 or 4?

 

[elviajero]

ADVERTISEMENT

Ignore my last question Stephen. I figured it out.

Cheers for the help.

 

[StephenCrump]

Thanks, you're welcome.

 

[elviajero]

ADVERTISEMENT

Stephen, could you help me further?

Number of Draws 6
10 Numbers in each draw, 0 - 9
1 number drawn in each draw

e.g.
5, 2, 3, 7, 8, 5

The chances of predicting the right order is 1: 1,000,000.

What formula is required to calculate the chance of predicting the 6 drawn numbers but in any order?

 

[StephenCrump]

You'll need COMBIN(n+k-1,k)

where:

n is 10 (the number of possibilities you're drawing from)
k is 6 (the number of draws)

= 1 in 5,005

 

[StephenCrump]

Sorry, I've only answered part of the question .... there are 5,005 possibilities but not all have equal probability.

If I predict that the numbers will be 1,1,1,1,1,1 then obviously I will have have only 1 in a million chance of success, i.e. 1 in 10^6.

But if I predict that the numbers will be 1,2,3,4,5,6 (in any order), I will have FACT(6)/1,000,000 chance of success.

Similarly, the probabilities will vary depending on how many doubles, triples etc you pick in your 6 numbers.

 

[StephenCrump]

Here's the complete table:

Columns B:G show possible number choices, e.g. 6 uniques, 4 uniques and one double, 3 uniques and one triple etc.
Columns I:N are helper columns.

I2: =10-SUM($A2:A2) copy across and down
P2: {=PRODUCT(COMBIN(I2:N2,B2:G2))} array-entered, copy down
Q2: {=FACT(6)/PRODUCT(FACT(C$1:G$1)^C2:G2)} array-entered, copy down
R2: =P2*Q2/1000000

The most likely outcome (45% chance) is 4 uniques and one double.
But the best strategy is to play 6 uniques, with has a 0.072% chance of winning.

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1		1	2	3	4	5	6		1	2	3	4	5	6		Combinations	Permutations	Probability
2		6							10	4	4	4	4	4		210	720	15.12%
3		4	1						10	6	5	5	5	5		1,260	360	45.36%
4		3		1					10	7	7	6	6	6		840	120	10.08%
5		2			1				10	8	8	8	7	7		360	30	1.08%
6		2	2						10	8	6	6	6	6		1,260	180	22.68%
7		1				1			10	9	9	9	9	8		90	6	0.05%
8		1	1	1					10	9	8	7	7	7		720	60	4.32%
9							1		10	10	10	10	10	10		10	1	0.00%
10				2					10	10	10	8	8	8		45	20	0.09%
11			3						10	10	7	7	7	7		120	90	1.08%
12			1		1				10	10	9	9	8	8		90	15	0.14%
13
14																5,005		100%

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

6​