rewrite VBA code : inverse Poisson function

[muppet77]

The poisson function in Excel gives

POISSON(10,10.88,TRUE) = 0.474289613

I would like some VBA to reverse this to find the 10.88, given 10 and also 0.474289613

ie

POISSONINV(10,0.474289613,TRUE) = 10.88

I found some code but it gets the 10 from the 10.88 and 0.474289613 and not the way i want it.

Could someone help me please to rearrange the VBA? I am a complete beginner.

thank you



***********************************************************

Function PoissonInv(dVal As Double, dMean As Double) As Variant
' shg 2011

' 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 ' number of events
Dim dCDF As Double ' cumulative distribution function of iX

' 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

******************************************************

 

[Norie]

How do you want the function to work?

 

[muppet77]

hi, so when I type

=POISSONINV(10,0.474289613)

it returns10.88

 

[shg]

The PoissonInv function returns the max number of events that have a cumulative probability <= a given probability.

What you want is the Poisson mean that results in a given number of events having a given cumulative probability. Try this:

Function PoissonMean(nEvents As Long, dProb As Double) As Double
    ' shg 2012
 
    ' Returns the mean that results in the given number of events
    ' having the specified cumulative probability
 
    Dim dMin        As Double
    Dim dMid        As Double
    Dim dMax        As Double
 
    dMin = 0
    dMax = 100000000#
    dMid = (dMin + dMax) / 2
 
    With WorksheetFunction
        Do While dSearchIndex(dMin, dMid, dMax, _
                              .Poisson(nEvents, dMid, True) < dProb)
            'Debug.Print .Poisson(nEvents, dMid, True)
        Loop
    End With
 
    PoissonMean = dMid
End Function
 
Function dSearchIndex(ByRef dMin As Double, _
                      ByRef dMid As Double, _
                      ByRef dMax As Double, _
                      bTooBig As Boolean) As Boolean
    ' shg 2011
 
    ' Changes the search limits dMin and dMax and
    ' the next test value dMid based on their
    ' current values and the results of the last test.
    ' Returns False when there are no more values to test.
 
    'Debug.Print dMin, dMid, dMax, IIf(bTooBig, "Too big", "Too small")
 
    If dMid = dMin Or dMid = dMax Then Exit Function
 
    If bTooBig Then dMax = dMid Else dMin = dMid
    dMid = (dMax + dMin) / 2
    dSearchIndex = True
End Function

E.g., =PoissonMean(10, 0.474289613) returns 10.88