Home 
Search 
Today's Posts 
#1




calculating probabilty of x number of successes with multiple tria
how do i calculate the probability of a certain number of successes when there are multiple trials each with different probabilities e.g. Trial P (of success) 1 0.2 2 0.4 3 0.9 4 0.6 5 0.5 6 0.4 7 0.7 8 0.3 9 0.1 10 0.5 11 0.6 12 0.7 13 0.9 14 0.1 15 0.2 16 0.2 17 0.2 18 0.5 19 0.6 20 0.7 21 0.9 How would i calculate the probability of 10 successes for example? I could calculate it manually, but there must be a quicker way of doing it using excel? cheers 
#2




calculating probabilty of x number of successes with multiple tria
You have not provided enough information to answer the question. What
follows assumes that the trials are independent. There are 352716=COMBIN(21,10) different ways to get 10 successes from 21 trials. You could enumerate them and sum the probability of each. For example, the probability that the first 10 trials are successes and the rest failures would be 3E09, as calculated by the array formula =PRODUCT(B2:B11)*PRODUCT(1B12:B22), assuming that the stated probabilities are in B2:B22. Of course this approach would be exceptionally tedious and time consuming. If it were my homework assignment, I would review properties of probability generating functions http://en.wikipedia.org/wiki/Probabi...ating_function and use 0.05=BINOMDIST(10,21,HARMEAN(B2:B22),FALSE) as a crude ballpark estimate to ensure that I had done the work correctly Jerry "mk9999999" wrote: how do i calculate the probability of a certain number of successes when there are multiple trials each with different probabilities e.g. Trial P (of success) 1 0.2 2 0.4 3 0.9 4 0.6 5 0.5 6 0.4 7 0.7 8 0.3 9 0.1 10 0.5 11 0.6 12 0.7 13 0.9 14 0.1 15 0.2 16 0.2 17 0.2 18 0.5 19 0.6 20 0.7 21 0.9 How would i calculate the probability of 10 successes for example? I could calculate it manually, but there must be a quicker way of doing it using excel? cheers 
#3




calculating probabilty of x number of successes with multiple
Sorry, I meant AVERAGE instead of HARMEAN for the ballpark calculation.
Jerry "Jerry W. Lewis" wrote: You have not provided enough information to answer the question. What follows assumes that the trials are independent. There are 352716=COMBIN(21,10) different ways to get 10 successes from 21 trials. You could enumerate them and sum the probability of each. For example, the probability that the first 10 trials are successes and the rest failures would be 3E09, as calculated by the array formula =PRODUCT(B2:B11)*PRODUCT(1B12:B22), assuming that the stated probabilities are in B2:B22. Of course this approach would be exceptionally tedious and time consuming. If it were my homework assignment, I would review properties of probability generating functions http://en.wikipedia.org/wiki/Probabi...ating_function and use 0.05=BINOMDIST(10,21,HARMEAN(B2:B22),FALSE) as a crude ballpark estimate to ensure that I had done the work correctly Jerry "mk9999999" wrote: how do i calculate the probability of a certain number of successes when there are multiple trials each with different probabilities e.g. Trial P (of success) 1 0.2 2 0.4 3 0.9 4 0.6 5 0.5 6 0.4 7 0.7 8 0.3 9 0.1 10 0.5 11 0.6 12 0.7 13 0.9 14 0.1 15 0.2 16 0.2 17 0.2 18 0.5 19 0.6 20 0.7 21 0.9 How would i calculate the probability of 10 successes for example? I could calculate it manually, but there must be a quicker way of doing it using excel? cheers 
#4




calculating probabilty of x number of successes with multiple
And, =BINOMDIST(10,21,AVERAGE(B2:B22),FALSE) evaluates to 0.17, not 0.05. I
still have the problem of Excel closing unexpectedly when I open Word http://groups.google.com/group/micro...d950cd9173c04b and I didn't adequately proofread what Excel recovered. Jerry "Jerry W. Lewis" wrote: Sorry, I meant AVERAGE instead of HARMEAN for the ballpark calculation. Jerry "Jerry W. Lewis" wrote: You have not provided enough information to answer the question. What follows assumes that the trials are independent. There are 352716=COMBIN(21,10) different ways to get 10 successes from 21 trials. You could enumerate them and sum the probability of each. For example, the probability that the first 10 trials are successes and the rest failures would be 3E09, as calculated by the array formula =PRODUCT(B2:B11)*PRODUCT(1B12:B22), assuming that the stated probabilities are in B2:B22. Of course this approach would be exceptionally tedious and time consuming. If it were my homework assignment, I would review properties of probability generating functions http://en.wikipedia.org/wiki/Probabi...ating_function and use 0.05=BINOMDIST(10,21,HARMEAN(B2:B22),FALSE) as a crude ballpark estimate to ensure that I had done the work correctly Jerry "mk9999999" wrote: how do i calculate the probability of a certain number of successes when there are multiple trials each with different probabilities e.g. Trial P (of success) 1 0.2 2 0.4 3 0.9 4 0.6 5 0.5 6 0.4 7 0.7 8 0.3 9 0.1 10 0.5 11 0.6 12 0.7 13 0.9 14 0.1 15 0.2 16 0.2 17 0.2 18 0.5 19 0.6 20 0.7 21 0.9 How would i calculate the probability of 10 successes for example? I could calculate it manually, but there must be a quicker way of doing it using excel? cheers 
#5




calculating probabilty of x number of successes with multiple
Hi jerry,
thanks for your help  i forgot to mention that the trials are independent of each other. your methods are all good but unfortunately i need an exact answer rather than an estimation. if this is not possible in excel would it be possible to do in any other porgrams? cheers "Jerry W. Lewis" wrote: And, =BINOMDIST(10,21,AVERAGE(B2:B22),FALSE) evaluates to 0.17, not 0.05. I still have the problem of Excel closing unexpectedly when I open Word http://groups.google.com/group/micro...d950cd9173c04b and I didn't adequately proofread what Excel recovered. Jerry "Jerry W. Lewis" wrote: Sorry, I meant AVERAGE instead of HARMEAN for the ballpark calculation. Jerry "Jerry W. Lewis" wrote: You have not provided enough information to answer the question. What follows assumes that the trials are independent. There are 352716=COMBIN(21,10) different ways to get 10 successes from 21 trials. You could enumerate them and sum the probability of each. For example, the probability that the first 10 trials are successes and the rest failures would be 3E09, as calculated by the array formula =PRODUCT(B2:B11)*PRODUCT(1B12:B22), assuming that the stated probabilities are in B2:B22. Of course this approach would be exceptionally tedious and time consuming. If it were my homework assignment, I would review properties of probability generating functions http://en.wikipedia.org/wiki/Probabi...ating_function and use 0.05=BINOMDIST(10,21,HARMEAN(B2:B22),FALSE) as a crude ballpark estimate to ensure that I had done the work correctly Jerry "mk9999999" wrote: how do i calculate the probability of a certain number of successes when there are multiple trials each with different probabilities e.g. Trial P (of success) 1 0.2 2 0.4 3 0.9 4 0.6 5 0.5 6 0.4 7 0.7 8 0.3 9 0.1 10 0.5 11 0.6 12 0.7 13 0.9 14 0.1 15 0.2 16 0.2 17 0.2 18 0.5 19 0.6 20 0.7 21 0.9 How would i calculate the probability of 10 successes for example? I could calculate it manually, but there must be a quicker way of doing it using excel? cheers 
#6




calculating probabilty of x number of successes with multiple
The generating function approach does give the exact answer, but is not
easily implemented in Excel. You could do the generating fuctions manually, or use a program that can do symbolic math, such as Maple, Mathematica, or the open source package Maxima http://maxima.sourceforge.net/index.shtml Jerry "mk9999999" wrote: Hi jerry, thanks for your help  i forgot to mention that the trials are independent of each other. your methods are all good but unfortunately i need an exact answer rather than an estimation. if this is not possible in excel would it be possible to do in any other porgrams? cheers 
#7




calculating probabilty of x number of successes with multipletria
On Jan 8, 9:39 am, Jerry W. Lewis wrote:
There are 352716=COMBIN(21,10) different ways to get 10 successes from 21 trials. You could enumerate them and sum the probability of each. [....] Of course this approach would be exceptionally tedious and time consuming. Only took less than 63 milliseconds for a VBA UDF to run through all combinations. Took less than 2.4 seconds for the UDF to sum the probability of exactly 10 successes over all combinations. Of course, YMMV depending on the speed of your system. Took 510 minutes to write the VBA UDF. To the OP (mk9999999): Is this a programming assignment or an assignment in statistics or probability? 
#8




calculating probabilty of x number of successes with multiple
I agree that modern computer exection time is quite fast, but for me the real
issue would be the time required to program and validate a routine that would run through all the combinations. That is the beauty of the generating function approach. Jerry "joeu2004" wrote: On Jan 8, 9:39 am, Jerry W. Lewis wrote: There are 352716=COMBIN(21,10) different ways to get 10 successes from 21 trials. You could enumerate them and sum the probability of each. [....] Of course this approach would be exceptionally tedious and time consuming. Only took less than 63 milliseconds for a VBA UDF to run through all combinations. Took less than 2.4 seconds for the UDF to sum the probability of exactly 10 successes over all combinations. Of course, YMMV depending on the speed of your system. Took 510 minutes to write the VBA UDF. To the OP (mk9999999): Is this a programming assignment or an assignment in statistics or probability? 
#9




calculating probabilty of x number of successes with multiple
On Jan 8, 8:37*pm, Jerry W. Lewis wrote:
I agree that modern computer exection time is quite fast, but for me the real issue would be the time required to program and validate a routine that would run through all the combinations. *That is the beauty of the generating function approach. Yes, stick with what you do best. As for the program development time: as I said, 510 minutes. Validation comes from scaling down to a tractable problem that can be solved easily by hand. That was the lion's share of the 5+ minutes. As trivial as the programming seems to be, I like to step through or add debug prints to be sure that everything is copacetic. As it turns out, my only error was in my "hand" calculations  probably a typo on the calculator. But I agree that a mathematical solution often provides greater insight. My real point was: the OP might consider a programming solution that does indeed enumerate all combinations exhaustively, if that is acceptable and easier for him/her. You dismissed the possibility based on incorrect presumptions ("exceptionally tedious and time consuming"). 
#10




calculating probabilty of x number of successes with multipletria
On Jan 8, 5:43*pm, I wrote:
Took less than 2.4 seconds for the UDF to sum the probability of exactly 10 successes over all combinations. *Of course, YMMV depending on the speed of your system. *Took 510 minutes to write the VBA UDF. Reduced to about 0.9 seconds with a "tedious", but obvious optimization. Makes the program look less "elegant". Add 23 minutes to the development time ;). 
Reply 

Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Why is Excel calculating a number?  Excel Discussion (Misc queries)  
Calculating Time with a number value  Excel Worksheet Functions  
Probabilty  Binomial  a simulation game?  Excel Worksheet Functions  
Text number to number value for calculating  Excel Worksheet Functions  
Calculating number of days  Excel Worksheet Functions 