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.

 

[elviajero]

Thank you very much.

That helps greatly to make sense of it.

I'm having problems getting the array to work (Mac for Excel 2011). I've tried (Control, Shift, Return) which creates an {array} but the result for column P2 is 210 for each row.

 

[StephenCrump]

I'm having problems getting the array to work (Mac for Excel 2011). I've tried (Control, Shift, Return) which creates an {array} but the result for column P2 is 210 for each row.

I'm afraid I'm 100% MacIgnorant.

Have you tried Cmd+Return?

 

[elviajero]

I'm afraid I'm 100% MacIgnorant.

Have you tried Cmd+Return?

Control+Shift+Return or Cmd+Return seem to create an array as in I get the array brackets { =PRODUCT(COMBIN(I2:N2,B2:G2)) } but for some reason its not calculating. I'll google it and see if I can find a fix.

 

[StephenCrump]

I just twigged ...

You'll get 210 in every cell in column P if you array-enter one formula over the range P2:P12.
Instead, array-enter the formula in just one cell P2. Then copy that cell down to P3:P12.
And similar for column Q.

If all else fails, try longhand formulae:

P2: =COMBIN(10,B2)*COMBIN(10-SUM(B2:B2),C2)*COMBIN(10-SUM(B2:C2),D2)*COMBIN(10-SUM(B2:D2),E2)*COMBIN(10-SUM(B2:E2),F2)*COMBIN(10-SUM(B2:F2),G2)

Q2: =FACT(6)/(FACT(C$1)^C2*FACT(D$1)^D2*FACT(E$1)^E2*FACT(F$1)^F2*FACT(G$1)^G2)

 

[elviajero]

I just twigged ...

You'll get 210 in every cell in column P if you array-enter one formula over the range P2:P12.
Instead, array-enter the formula in just one cell P2. Then copy that cell down to P3:P12.
And similar for column Q.

If all else fails, try longhand formulae:

P2: =COMBIN(10,B2)*COMBIN(10-SUM(B2:B2),C2)*COMBIN(10-SUM(B2:C2),D2)*COMBIN(10-SUM(B2:D2),E2)*COMBIN(10-SUM(B2:E2),F2)*COMBIN(10-SUM(B2:F2),G2)

Q2: =FACT(6)/(FACT(C$1)^C2*FACT(D$1)^D2*FACT(E$1)^E2*FACT(F$1)^F2*FACT(G$1)^G2)

Yes you're right. I was highlighting the whole range and not copying down from P2. I haven't used an array in years but seem to recall the need to highlight the range. Anyway, its working now. Thanks for the longhand, that helps too.