Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 8th 08, 03:57 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2008
Posts: 2
Default 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   Report Post  
Old January 8th 08, 05:39 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 837
Default 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

  #3   Report Post  
Old January 8th 08, 05:43 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 837
Default 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

  #4   Report Post  
Old January 8th 08, 05:57 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 837
Default 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

  #5   Report Post  
Old January 8th 08, 06:39 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2008
Posts: 2
Default 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



  #6   Report Post  
Old January 8th 08, 11:58 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 837
Default 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   Report Post  
Old January 9th 08, 01:43 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 2,059
Default 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?
  #8   Report Post  
Old January 9th 08, 04:37 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 837
Default 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?

  #9   Report Post  
Old January 9th 08, 06:00 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 2,059
Default 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").
  #10   Report Post  
Old January 9th 08, 06:27 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 2,059
Default 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 ;-).


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Why is Excel calculating a number? Gator Excel Discussion (Misc queries) 2 January 4th 08 06:08 PM
Calculating Time with a number value Corey Excel Worksheet Functions 2 December 28th 06 05:29 AM
Probabilty - Binomial - a simulation game? Rob Eyes Excel Worksheet Functions 4 May 2nd 06 05:36 PM
Text number to number value for calculating ddiebold7 Excel Worksheet Functions 3 June 13th 05 12:48 PM
Calculating number of days 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