Option Explicit

Private Sub RegisterMyFunction()
    Application.MacroOptions _
        Macro:="PoissonInverse", _
        Description:="Returns the smallest value for which the cumulative poisson distribution is greater than or equal to a criterion", _
        Category:="Statistical", _
        ArgumentDescriptions:=Array( _
            "probability to calculate against", _
            "mean of distribution")
End Sub

Function PoissonInverse(Probability As Double, lambda As Double) As Double

Dim CheckPoisson As Double
Dim EstimatedX As Double

'Error checking:
'If any inputs are non-values

If Not (IsNumeric(Probability) Or IsNumeric(lambda)) Then

    PoissonInverse = CVErr(xlErrValue)
    Exit Function

End If
    
'Probability is not within calculatable bounds
'or invalid mean
If Probability < 0 Or Probability >= 1 Or lambda <= 0 Then
    
    PoissonInverse = CVErr(xlErrNum)
    Exit Function

End If

'Lower bound of cumulative distribution function
'if probability 0 then return 0

If Probability = 0 Then
    
    PoissonInverse = 0
    Exit Function

End If

'Setting up Bionomial Inverse test in Excel to find rough whereabouts of result
'Using a value of lambda/probability as number of trials

Dim NumberOfTrials As Double
NumberOfTrials = lambda / Probability
EstimatedX = WorksheetFunction.Binom_Inv(NumberOfTrials, lambda / NumberOfTrials, Probability)


'Find initial poisson calculation based on Bionomial guess
CheckPoisson = WorksheetFunction.Poisson_Dist(EstimatedX, lambda, True)

'Check that the value of the poisson of esimated X-value is the correct one, limited at 0
'And cycle through until the cumulative probability is strictly less than the provided probability
'in either direction and return the result

If CheckPoisson > Probability Then

    Do Until CheckPoisson < Probability
    
        EstimatedX = EstimatedX - 1
        
        If EstimatedX = -1 Then
            Exit Do
        Else
            CheckPoisson = WorksheetFunction.Poisson_Dist(EstimatedX, lambda, True)
        End If
        
    Loop

    EstimatedX = EstimatedX + 1

Else

    Do Until CheckPoisson > Probability
    
        EstimatedX = EstimatedX + 1
        CheckPoisson = WorksheetFunction.Poisson_Dist(EstimatedX, lambda, True)
        
    Loop

End If

PoissonInverse = EstimatedX

End Function