Originally Posted by
FourLengthsClear
Notwithstanding donjuan's (correct) observation, you would need to:
a) Familiarise yourself with the =Poisson function in MS Excel (it does the same as Ganchrow's Calculator).
b) Be able to "invert" the Poisson equation to give you the expected mean
The following should do b) if you enter it as a new function in MS Excel via VBA (google it if unsure, it is quite easy to do).
Function PoissonInv(dVal As Double, dMean As Double) As Variant
' flc
' For a Poisson process with mean dMean, _
' returns the largest integer such that the CDF <= dVal
' E.g., =POISSON(5, 10, TRUE) returns 0.0670859629
' PoissonInv(0.0670859629, 10) returns 5
Dim iX As Long
Dim dCDF As Double
' these variables are used to simplify this summation:
' dCDF = dCDF + Exp(-dMean) * dMean ^ iX / .Fact(iX)
Dim dExpMean As Double ' =Exp(-dMean)
Dim dFact As Double ' incremental factorial
Dim dPowr As Double ' incremental power
If dVal < 0 Or dVal >= 1 Then
PoissonInv = CVErr(xlErrValue)
ElseIf dVal > 0 Then
dExpMean = Exp(-dMean)
dFact = 1
dPowr = 1
Do While dCDF < dVal
dCDF = dCDF + dExpMean * dPowr / dFact
iX = iX + 1
dFact = dFact * iX
dPowr = dPowr * dMean
Loop
PoissonInv = iX - IIf(dCDF / dVal > 1.000000000001, 2, 1)
End If
End Function