Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Poisson Calculations
hi all,
i would like to know whether i can / how to calculate the mean value of occurrences from a given percentage probability for a given number of occurrences. for example, if know that there is a 40% chance there will be 2 or less occurrences, how do i calculate the mean number of occurrences? (i will then use this value as the mean part of various poisson equations to work out the probability of various numbers of occurrences) . thanks for your help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Poisson Calculations
Probability of 2 or less occurances is =POISSON(2,mean,TRUE)
Given that, there are various ways to answer your question: - You can plot this by various values of mean and get a rough visual estimate of what mean gives a value of 0.4. - You can use Solver to numerically find the mean that makes this equal to 0.4 - You can download Ian Smith's probabilty library http://members.aol.com/iandjmsmith/examples.xls and use it to calculate =ucb_poisson(2,0.4) - A less accurate implementation of the theoretically exact calculation for a poisson upper confidence bound would be to use the native Excel functions =GAMMAINV(1-0.4,x+1,1) or =CHIINV(0.4,2*(2+1))/2, where x=2 for your question. The Poisson mean that has exactly 40% probability of 2 or less is 3.105379... Jerry " wrote: hi all, i would like to know whether i can / how to calculate the mean value of occurrences from a given percentage probability for a given number of occurrences. for example, if know that there is a 40% chance there will be 2 or less occurrences, how do i calculate the mean number of occurrences? (i will then use this value as the mean part of various poisson equations to work out the probability of various numbers of occurrences) . thanks for your help. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Poisson Calculations
=GAMMAINV(1-0.4,x+1,1)
Hi Jerry. Very nice. For fun, here's my vba attempt at trying to beat Excel's 7-9 digit accuracy. If the op's " # of occurrences" is an integer, than the Newton method appears to have terms that cancel. Hence the following idea: ' Check for occurrences of 2 & 10 Sub Testit() Debug.Print FindMean(2, 0.4) Debug.Print FindMean(10, 0.4) End Sub Function FindMean(val, perc) Dim p Dim k Dim c Dim m Dim j As Long Dim cnt As Long m = val ' Best Guess for m p = perc With WorksheetFunction For cnt = 1 To 15 k = 0 p = val For j = 0 To val c = .Permut(val, j) k = k + c * m ^ p p = p - 1 Next j m = m - (c * Exp(m) * perc - k) / (m ^ val) Next cnt End With FindMean = m End Function Returns: 3.10537859726335 11.5153304496102 If I check it with a math program: InverseGammaRegularized[1+2,Infinity,-0.4] 3.10537859726335 InverseGammaRegularized[1+10,Infinity,-0.4] 11.51533044961022 They appear to match pretty well. Excel worksheet functions had the following which we know to be caused by GAMMAINV not being very accurate. 3.10537859748574 11.51533046679880 Anyway, very interesting. -- Dana DeLouis "Jerry W. Lewis" wrote in message ... Probability of 2 or less occurances is =POISSON(2,mean,TRUE) Given that, there are various ways to answer your question: - You can plot this by various values of mean and get a rough visual estimate of what mean gives a value of 0.4. - You can use Solver to numerically find the mean that makes this equal to 0.4 - You can download Ian Smith's probabilty library http://members.aol.com/iandjmsmith/examples.xls and use it to calculate =ucb_poisson(2,0.4) - A less accurate implementation of the theoretically exact calculation for a poisson upper confidence bound would be to use the native Excel functions =GAMMAINV(1-0.4,x+1,1) or =CHIINV(0.4,2*(2+1))/2, where x=2 for your question. The Poisson mean that has exactly 40% probability of 2 or less is 3.105379... Jerry " wrote: hi all, i would like to know whether i can / how to calculate the mean value of occurrences from a given percentage probability for a given number of occurrences. for example, if know that there is a 40% chance there will be 2 or less occurrences, how do i calculate the mean number of occurrences? (i will then use this value as the mean part of various poisson equations to work out the probability of various numbers of occurrences) . thanks for your help. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Poisson Calculations
=GAMMAINV(1-0.4,x+1,1)
sorry for the delay, many thanks for your help. just what i needed. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Poisson Calculations
Better late than never; thanks for the feedback.
Jerry " wrote: =GAMMAINV(1-0.4,x+1,1) sorry for the delay, many thanks for your help. just what i needed. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Will excel generate one-tailed confidence intervals for Poisson distribution? | Excel Worksheet Functions | |||
Question about use of Poisson probabilities | Excel Worksheet Functions | |||
Question about use of Poisson probabilities | Excel Discussion (Misc queries) | |||
Modelling arrival intervals using Poisson distributions | Excel Discussion (Misc queries) | |||
Poisson Distribution | Excel Discussion (Misc queries) |