table of probably calculations

[excelNewbie22]

hi!
i'm trying to figure out some calculation through excel

if i need to choose 6 numbers out of 37
but i want to force that 1 number will return from last draw (1-2-3-4-5-6)
so i thought:
COMBIN(36,5)*6 = 376,992 x 6 = 2,261,952
while the all is =COMBIN(37,6) = 2,324,784

but if for example i took from last draw the number 2 and the next five can be from the 36 remaining, let's say 2-4-10-11-22-33
but if i take the number 4 and the next five could be the same 4-2-10-11-22-33
is that right? the calc?

same for 2 from last draw and 4 out of 35 remaining
=COMBIN(35,4)*COMBIN(6,2)
or 2 from last 2 draws
=COMBIN(35,4)*COMBIN(12,2)
3455760 which is wrong....why?
and so on....

if it is, i want to build in excel a larger table with same principles, but in larger scale, draws, and etc

 

[StephenCrump]

You start off correctly. For any given number, the probability is COMBIN(36,5)/COMBIN(37,6) = ~16% that this number will appear in a random draw.

But for six numbers, you can't simply multiply this probability by 6, as the combinations aren't mutually exclusive, i.e. they overlap - many of the combinations containing 1 will also contain some combination of 2, 3, 4, 5 and 6.

The probability of a random draw containing at least one of your 6 numbers will be 1-COMBIN(31,6)/COMBIN(37,6) = ~68%

The probability of a random draw containing exactly one of your 6 numbers will be 6 * COMBIN(31,5)/COMBIN(37,6) = ~44%

 

[excelNewbie22]

thank you Stephen for the answer,
i'm not quite good with this,
so when you wrote 68% it means i've left with COMBIN(37,6)-COMBIN(31,6) = 1,588,503 combinations?

also, what i'm always looking is at least
so for 2 numbers from two last draws
(assuming last two draws have all different numbers)
will it be 1-COMBIN(25,6)/COMBIN(37,6) ?

and for 2 from one last draw yet to understand... any chance for 1-COMBIN(33,6)/COMBIN(37,6) ?

 

[StephenCrump]

If you pick N numbers (where N is 1 to 6), then there are COMBIN(37-N,6-N) ways to choose the remaining 6-N numbers. So, for example, if you want to lock in two numbers, then there are COMBIN(35,4) ways to choose the other four numbers. This means there is a COMBIN(35,4)/COMBIN(37,6) or ~2.3% probability that both your numbers come up in a random draw of 6 numbers.

And you can assume a lottery draw is random - the lottery organisers go to some trouble to make sure it is. Hence it doesn't matter how you choose your numbers - for example, 1 and 2, numbers that came up last week, numbers that didn't come up last week etc etc - the probabilities will be the same.

so when you wrote 68% it means i've left with COMBIN(37,6)-COMBIN(31,6) = 1,588,503 combinations?

If you choose 6 numbers out of 37, that leaves 31 numbers, with COMBIN(31,6) ways to select six numbers out of these 31. So out of all possible COMBIN(37,6) combinations, COMBIN(31,6) will contain none of your chosen six numbers. Hence 1- COMBIN(31,6)/combin(37,6) or ~68% is the probability that a random draw of 6/37 will contain either 1, 2, 3, 4, 5 or all 6 of your chosen numbers. The probability of one number matching is ~44% (Post #2). 6 matches will have probability 1/COMBIN(37,6), i.e.virtually nil. Here are all six possibilities:

also, what i'm always looking is at least
so for 2 numbers from two last draws
(assuming last two draws have all different numbers)
will it be 1-COMBIN(25,6)/COMBIN(37,6) ?

Yes, if you select 12 numbers, there is a 1-COMBIN(25,6)/COMBIN(37,6) or ~92.4% chance that at least one of your chosen numbers appears in a random draw of 6. Here all all six possibilities:

 

[excelNewbie22]

thank you very much stephen!