Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lottery...
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lottery...
I would do it like this...
A1 = Total available B1 = First amount drawn C1 = 2nd amount drawn D1 = IF(OR(B1A1,C1B1),"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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lottery...
"Jim Cone" wrote in message ... I would do it like this... A1 = Total available B1 = First amount drawn C1 = 2nd amount drawn D1 = IF(OR(B1A1,C1B1),"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" wrote in message ... 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. 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% |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lottery...
"Jake" wrote in message ... "Jim Cone" wrote in message ... I would do it like this... A1 = Total available B1 = First amount drawn C1 = 2nd amount drawn D1 = IF(OR(B1A1,C1B1),"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" wrote in message ... 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. 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lottery...
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" 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% "Jim Cone" wrote in message ... I would do it like this... A1 = Total available B1 = First amount drawn C1 = 2nd amount drawn D1 = IF(OR(B1A1,C1B1),"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" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lottery...
"Jim Cone" wrote in message ... 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" 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% "Jim Cone" wrote in message ... I would do it like this... A1 = Total available B1 = First amount drawn C1 = 2nd amount drawn D1 = IF(OR(B1A1,C1B1),"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" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lottery...
"Jim Cone" wrote in message ... 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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lottery...
On May 31, 3:43*am, "Jake" wrote:
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(D2:D7) 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). |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lottery...
Minor clarification....
On May 31, 8:23*am, joeu2004 wrote: =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. I wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lottery | Excel Discussion (Misc queries) | |||
Lottery combinations | Excel Worksheet Functions | |||
Lottery Model | Excel Worksheet Functions | |||
Lottery exercise | Excel Worksheet Functions | |||
lottery drawing | Excel Worksheet Functions |