# Chances

#### elviajero

##### Board Regular
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.

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### StephenCrump

##### MrExcel MVP
Welcome to the Forum!

Excel 2010
AB
1NoProb.
2059.3%
3133.1%
426.9%
530.6%
640.0%

</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

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

#### elviajero

##### Board Regular
Welcome to the Forum!

Excel 2010
AB
1NoProb.
2059.3%
3133.1%
426.9%
530.6%
640.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

##### Board Regular
Ignore my last question Stephen. I figured it out.

Cheers for the help.

#### StephenCrump

##### MrExcel MVP
Thanks, you're welcome.

#### elviajero

##### Board Regular
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

##### MrExcel MVP
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

##### MrExcel MVP
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

##### MrExcel MVP
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
ABCDEFGHIJKLMNOPQR
1123456123456CombinationsPermutationsProbability
26104444421072015.12%
34110655551,26036045.36%
431107766684012010.08%
5211088877360301.08%
62210866661,26018022.68%
71110999989060.05%
81111098777720604.32%
911010101010101010.00%
10210101088845200.09%
11310107777120901.08%
12111010998890150.14%
13
145,005100%

</tbody>
6

Replies
6
Views
329
Replies
1
Views
371
Replies
1
Views
342
Replies
7
Views
324
Replies
13
Views
822

1,195,951
Messages
6,012,498
Members
441,702
Latest member
chacham

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back