Quote:
Originally Posted by chinagambler
What's the Excel formula to calculate the chances that an event with a probability p of occuring will occur at least x times out of n trials? I remember it had something to do with combine.
It does, but there's not just some simple formula.
Say you pick a random card from a deck 10 times (putting it back each time), and you want to know the probability you will get a club at least 7 times. Well, there are 4 ways that can happen: we can get exactly 7 clubs, or exactly 8 clubs, or exactly 9 clubs, or exactly 10 clubs. If we can find the probability that each case happens, we'll add those together to get our answer.
Now, if you want to know the probability you get exactly 7 clubs, you can do as follows: the probability of getting a club if you pick one card is 0.25, and the probability of getting a non-club is 0.75. So the probability of a particular sequence of seven clubs and three non-clubs (say, the probability your first 7 picks are clubs and the last 3 are not) is just the product of your individual probabilities: (0.25)^7 * (0.75)^3. But there are a lot of different sequences where we can get 7 clubs and 3 not. In effect we need to choose which 7 of the 10 cards will be clubs, which can be done in 10C7 (or in the notation used above, COMBINE(10, 7) ) ways. The notation nCk just means 'the number of ways to choose a set of k things from a set of n things'. In general, nCk is equal to n!/[ k! (n-k)! ] , so 10C7 is equal to 10!/ [ 7! * 3! ]. Finally if we multiply the probability we get a particular sequence containing 7 clubs by the number of all such sequences, we find the probability of getting exactly 7 clubs:
10C3 * (0.25)^7 * (0.75)^3
= 10! /[ (3!)(7!) ] * (0.25)^7 * (0.75)^3
Similarly you can do that for exactly 8, 9 and 10 clubs and add to get your answer:
10C3 * (0.25)^7 * (0.75)^3 + 10C2 * (0.25)^8 * (0.75)^2 + 10C1 * (0.25)^9 * (0.75)^1 + 10C10 * (0.25)^10
Not quite as simple as you were probably hoping for!
This whole topic goes by the name 'binomial probability' if you want to read up on it somewhere.