Jake said:
A1 = total number of balls in the lottery, eg 49
A2 = number of balls drawn in the game, eg 6
A3 = number of balls matched by player, eg 6 [....]
I would like a formula to calculate odds for variations
in value of A1, A2 and A3.
Instead of using A3, I find it useful to generate all possible number of
matches, A2 to zero. It serves as a check on the methodology: the sum
should equal the total possible. Then we can exclude any matches that the
lottery does not pay for, typically 0, 1 and 2. (You will see why below.)
If the presentation below is unclear due to formatting, see preview image or
download the Excel file "lottery odds.xls" at
http://www.box.com/s/uv8tggcbe1kvypqiynju.
The total possible combinations is =COMBIN(A1,A2). Set up the following
table.
#Balls #Possible
Row Match Matches Probability Odds[*]
6: 5 1 0.000026% 1:3,819,816
7: 4 255 0.006676% 1:14,980
8: 3 12750 0.333786% 1:300
9: 2 208250 5.451833% 1:18
10: 1 1249500 32.711000% 1:3
11: 0 2349060 61.496679% 1:2
12: 3819816
[*] I am using the term "odds" the same way that Mega Lottery and other
lotteries I am familiar with. But that is one too high according to the way
that "odds" is usually defined. See
http://en.wikipedia.org/wiki/Odds. (I
do not agree with everything in that wiki page.)
Enter the following formulas:
A6: =A2
A7: =A2-1
B6: =COMBIN($A$2,A6)*COMBIN($A$1-$A$2,$A$2-A6)
C6: =B6/$B$12
D6: ="1:"&TEXT($B$12/B6,"#,##0")
B12: =SUM(B6:B11)
Copy A7 down until zero.
Copy B6
6 down for the same number of rows.
After the last row, enter a formula similar to B12 in this example.
Explanation of B6....
COMBIN(A2,A6) is the number of ways A6 numbers match A2 balls drawn.
COMBIN(A1-A2,A2-A6) is the number of ways the remaining A2-A6 numbers do not
match any of the A2 balls drawn; that is, the number of ways the remaining
A2-A6 numbers can drawn from the remaining A1-A2 balls.
The second term ensures that we count exactly A6 matches. Otherwise, for
example, if one of the remaining A2-A6 numbers matches a drawn ball, we are
really talking about A6+1 matches, not A6. (This might be better understood
by considering A7 and fewer matches.)
PS: As you can see, the probability of matching 0, 1 and 2 balls is
relatively high. Presumably for that reason, the lotteries I am familiar
with do not pay for those matches.
-----
The lotteries I am familiar with also have a "bonus" ball (aka "mega" ball).
That greatly increases the total possible combinations, which reduces the
chances of matching.
Suppose A3 contains the number of "mega" balls (e.g. 46). The total
possible combinations is A3*COMIN(A1,A2). Set up the following table.
#Balls #Possible
Row Match Matches Probability Odds[*]
15: 5+M 1 0.00000057% 1:175,711,536
16: 5 45 0.00002561% 1:3,904,701
17: 4+M 255 0.00014512% 1:689,065
18: 4 11,475 0.00653059% 1:15,313
19: 3+M 12,750 0.00725621% 1:13,781
20: 3 573,750 0.32652950% 1:306
21: 2+M 208,250 0.11851811% 1:844
22: 2 9,371,250 5.33331517% 1:19
23: 1+M 1,249,500 0.71110869% 1:141
24: 1 56,227,500 31.99989100% 1:3
25: 0+M 2,349,060 1.33688434% 1:75
26: 0 105,707,700 60.15979509% 1:2
27: 175,711,536
Enter the following formulas:
A15: =A16&"+M"
A16: =A2&""
A17: =A18&"+M"
A18: =A16-1&""
B15: =COMBIN($A$2,LEFT(A15,1))*COMBIN($A$1-$A$2,$A$2-LEFT(A15,1))
*IF(OR($A$3=0,RIGHT(A15,1)="m"),1,$A$3-1)
C15: =B15/$B$27
D15: ="1:"&TEXT($B$27/B15,"#,##0")
Copy A17:A18 down until zero.
Copy B15:C15 down for the same number of rows.
After the last row, enter a formula similar to B27 in this example.
The explanation of B15 is essentially the same as for B6 above. But when
column A does not contain an "M", we multiply by the number of ways of not
matching the drawn "mega" ball; that is, the remaining numbers that do not
match the drawn "mega" ball, namely A3-1.
