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