Lottery...

[Jake]

If I have A2 numbers in a pot and I select B2 of them, then draw out C2, how
do I write a formula that will calculate the chance of the number of numbers
drawn in C2 matching (some or all, depending on whether B2 = C2, but B2 is
always >= C2) the number of numbers in B2?

Example 100 balls, 100 to match, 100 drawn - chances are 1:1 that there will
be a match - or just displayed in an cell 1.

I know how do do the chance that A2 ball in B2 will match by using
=COMBIN(B2,A2) but thats different to what I'm asking.

 

[Jim Cone]

I would do it like this...

A1 = Total available
B1 = First amount drawn
C1 = 2nd amount drawn
D1 = IF(OR(B1>A1,C1>B1),"error",B1/A1*C1/A1)

Beware as the above from a one statistics class student.
'---
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(Lottery Numbers workbook: two national lotteries & twelve state lotteries - in the free folder)




"Jake" <[email protected]>
wrote in message
news:[email protected]...

 

[Jake]

I would do it like this...

A1 = Total available
B1 = First amount drawn
C1 = 2nd amount drawn
D1 = IF(OR(B1>A1,C1>B1),"error",B1/A1*C1/A1)

Beware as the above from a one statistics class student.
'---
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(Lottery Numbers workbook: two national lotteries & twelve state
lotteries - in the free folder)




"Jake" <[email protected]>
wrote in message

Thanks, but my result seems to be giving me a better chance of matching 5
than of matching only one, hehe

10 balls total, you plan on drawing 5 balls out so you write down 5 numbers
you hope to draw, then pick 5 balls out of a hat. Chance of matching at
least 1 ball out of those 5 drawn says its 0.05, when it should be evens at
least. Chances of matching all five balls appears to be 0.25 or one in four?

If I have 10 balls total and I draw out 10 balls, the chances of me matching
at least 1, 2, 3 or all the way up to 10 should be 1 or 100%

 

[Jake]

Thanks, but my result seems to be giving me a better chance of matching 5
than of matching only one, hehe

10 balls total, you plan on drawing 5 balls out so you write down 5
numbers you hope to draw, then pick 5 balls out of a hat. Chance of
matching at least 1 ball out of those 5 drawn says its 0.05, when it
should be evens at least. Chances of matching all five balls appears to be
0.25 or one in four?

If I have 10 balls total and I draw out 10 balls, the chances of me
matching at least 1, 2, 3 or all the way up to 10 should be 1 or 100%

Oh yeah, and remember when 1 ball has been drawn then there will only be n-1
more balls left to draw from.

 

[Jim Cone]

Chances of any ball matching when you draw 5 balls from a total of 10 is 50%: =B1/A1
Chances of a single ball drawn from the batch of 5 matching is 5%: = C1/A1 (10%) x B1/A1 (50%)
Anything beyond that is above my skill level. (i may already be there)
'---
Jim Cone



"Jake" <[email protected]>
wrote in message

 

[Jake]

Chances of any ball matching when you draw 5 balls from a total of 10 is
50%: =B1/A1
Chances of a single ball drawn from the batch of 5 matching is 5%: = C1/A1
(10%) x B1/A1 (50%)
Anything beyond that is above my skill level. (i may already be there)
'---
Jim Cone



"Jake" <[email protected]>
wrote in message

 

[Jake]

Chances of any ball matching when you draw 5 balls from a total of 10 is
50%: =B1/A1
Chances of a single ball drawn from the batch of 5 matching is 5%: = C1/A1
(10%) x B1/A1 (50%)
Anything beyond that is above my skill level. (i may already be there)
'---
Jim Cone

When you select your 1st number, you have 10 numbers to choose from, and...a
1 in 10 chance of picking the right one.

When you select your 2nd number, you have 9 numbers to choose from, and...a
1 in 9 chance of picking the right one.

When you select your 3rd number, you have 8 numbers to choose from, and...a
1 in 8 chance of picking the right one. etc.

In order to win, you have to pick the first number right AND the second
number right AND the third number right, etc. In the language of statistics,
AND usually means to multiply.
So, to figure out your odds of winning, multiply together all of the
fractional odds of picking a given number correctly



1/10 × 1/9 × 1/8 × 1/7 × 1/6 = 1/30240



So, at this point, your odds of winning are 1 in 30240. But, since you can
choose your winning numbers in any order, your chances of winning are
somewhat better than this. Your chance betters by the number of different
ways that a sequence of 5 numbers can be written down, which for 5 numbers
is 5! (5 factorial) or 120. Divide 30240 by 120 to account for this, to get
252.

In other words, there are 120 different ways that the 5 numbers you choose
can be filled out - if you choose your 5 numbers correctly, any of these
ways will make a winning ticket.

Essentially I need to write this in a formula that will calculate the
chances of getting [say] 3 numbers correct when you draw five balls from a
pot of 10, and this formula will work to calculate the chances of getting X
numbers correct when you draw Y balls from a set of Z size.

 

[joeu2004]

Essentially I need to write this in a formula that will
calculate the chances of getting [say] 3 numbers correct
when you draw five balls from a pot of 10, and this formula
will work to calculate the chances of getting X numbers
correct when you draw Y balls from a set of Z size.

..... Where X is C2, Y is B2 and Z is A2 in your original question.
Try the folowing in D2:

=COMBIN($A$2-$B$2,$B$2-C2)*COMBIN($B$2,C2)

Suppose A2 is 56 and B2 is 5. I wrote the formula above so that you
can put 0 through 5 (B2) into C2 through C7, and copy the formula down
through D7. Then, as a check, SUM(D27) should equal the total
number of ways to choose 5 from 56, namely COMBIN(A2,B2).

Explanation: In a set of B2, COMBIN(B2,C2) is the number of ways to
match C2. Then, COMBIN(A2-B2,B2-C2) is the number of ways that the
remainder of the set, B2-C2, does not match any of the B2 numbers
drawn; that is, matches the remainder of the set drawn from, A2-B2.

Computational note: Mathematically, COMBIN(n,k) is computed by
FACT(n)/(FACT(k)*FACT(n-k)). Since Excel uses binary floating-point
to represent numbers and to do computation, Excel can represent only
integers up to 2^53 exactly. Thus, obstensibly, FACT(17) is the
largest factorial that we can be sure is calculated accurately. In
fact, larger factorials can be calculated accurately, by coincidence.
And there are ways to compute COMBIN so as to extend the range of
accuracy.

But the point is: for some combination of A2 and B2, the sum of the
formula above (D2) might not exactly equal COMBIN(A2,B2).

 

[joeu2004]

Minor clarification....

=COMBIN($A$2-$B$2,$B$2-C2)*COMBIN($B$2,C2)

Explanation:  In a set of B2 numbers, COMBIN(B2,C2) is the number
of ways to match C2 numbers.  Then, COMBIN(A2-B2,B2-C2) is the
number of ways that the remainder of the set, B2-C2 numbers, does
not match any of the B2 numbers drawn; that is, the number of ways it
matches the remainder of the set drawn from, A2-B2 numbers.

Excel can represent only integers up to 2^53 exactly. Thus,
obstensibly, FACT(17) is the largest factorial that we can
be sure is calculated accurately.  In fact, larger factorials
can be calculated accurately, by coincidence.

Errata: FACT(18) is the largest factorial less than 2^53; ergo, it is
the largest factorial that we can be sure is calculated accurately,
namely 6402373705728000.

But by coincidence, FACT(22) is the largest factorial calculated
accurately, namely 1124000727777607680000.

However, note that Excel formats only the first 15 significant digits,
rounding the 16th digit and substituting zeros for any remaining
digits after the first 15 digits.

So FACT(17) is the largest factorial that we can be sure Excel
displays accurately, namely 355687428096000.

But by coincidence, FACT(20) is the largest factorial that Excel
displays accurately, namely 2432902008176640000.