Remember Me? January 8th 08, 03:57 PM posted to microsoft.public.excel.worksheet.functions
 mk9999999 external usenet poster First recorded activity by ExcelBanter: Jan 2008 Posts: 2 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 January 8th 08, 05:39 PM posted to microsoft.public.excel.worksheet.functions
 Jerry W. Lewis external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 837 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 3E-09, as calculated by the array formula
=PRODUCT(B2:B11)*PRODUCT(1-B12: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 ball-park
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 January 8th 08, 05:43 PM posted to microsoft.public.excel.worksheet.functions
 Jerry W. Lewis external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 837 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 3E-09, as calculated by the array formula
=PRODUCT(B2:B11)*PRODUCT(1-B12: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 ball-park
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 January 8th 08, 05:57 PM posted to microsoft.public.excel.worksheet.functions
 Jerry W. Lewis external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 837 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 3E-09, as calculated by the array formula
=PRODUCT(B2:B11)*PRODUCT(1-B12: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 ball-park
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 January 8th 08, 06:39 PM posted to microsoft.public.excel.worksheet.functions
 mk9999999 external usenet poster First recorded activity by ExcelBanter: Jan 2008 Posts: 2 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 3E-09, as calculated by the array formula
=PRODUCT(B2:B11)*PRODUCT(1-B12: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 ball-park
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 January 8th 08, 11:58 PM posted to microsoft.public.excel.worksheet.functions
 Jerry W. Lewis external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 837 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 January 9th 08, 01:43 AM posted to microsoft.public.excel.worksheet.functions
 joeu2004 external usenet poster First recorded activity by ExcelBanter: Jan 2007 Posts: 2,059 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 5-10 minutes to
write the VBA UDF.

To the OP (mk9999999): Is this a programming assignment or an
assignment in statistics or probability? January 9th 08, 04:37 AM posted to microsoft.public.excel.worksheet.functions
 Jerry W. Lewis external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 837 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 5-10 minutes to
write the VBA UDF.

To the OP (mk9999999): Is this a programming assignment or an
assignment in statistics or probability? January 9th 08, 06:00 AM posted to microsoft.public.excel.worksheet.functions
 joeu2004 external usenet poster First recorded activity by ExcelBanter: Jan 2007 Posts: 2,059 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, 5-10 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"). January 9th 08, 06:27 AM posted to microsoft.public.excel.worksheet.functions
 joeu2004 external usenet poster First recorded activity by ExcelBanter: Jan 2007 Posts: 2,059 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 5-10 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 2-3 minutes
to the development time ;-).

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post Gator Excel Discussion (Misc queries) 2 January 4th 08 06:08 PM Corey Excel Worksheet Functions 2 December 28th 06 05:29 AM Rob Eyes Excel Worksheet Functions 4 May 2nd 06 05:36 PM ddiebold7 Excel Worksheet Functions 3 June 13th 05 12:48 PM Carlos Excel Worksheet Functions 3 March 1st 05 08:23 PM

All times are GMT +1. The time now is 01:03 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd. Copyright ©2004-2021 ExcelBanter.
The comments are property of their posters.

# About Us

"It's about Microsoft Excel"

Copyright © 2017