Inverse Poisson in Excel

[Skippy]

I had need to write an inverse poisson function recently (since for some reason excel doesn't ship with this). Code is as follows:

Function InversePoisson(rand, lambda) As Long
Dim i As Long
Dim Fx As Double
Dim px As Double

On Error GoTo ErrorEndSub

px = Exp(-lambda)
Fx = px
If rand < Fx Then
InversePoisson = 0
Exit Function
End If
For i = 1 To lambda * 4

px = px * lambda / i
Fx = Fx + px
If rand < Fx Then
InversePoisson = i
Exit Function
End If
Next i
ErrorEndSub:
InversePoisson = 0
Exit Function
End Function

Can anyone tell me how I can get it to return a "#NUM!" or "#VALUE!" error if, for example, the probability passed to it is not in the range 0 to 1, or the lambda is not a positive integer? I can write the error handling code for this, but can't work out how to set the vaule to one of these Excel error values. Any pointers?

[NoName]

Check out CVErr in VBA help.

[Brad Gile]

Aside from the use of cverr, you have other problems.

1. Rand is a keyword (the Rand() function). Keywords should NEVER be used as variable names. Excel should have barfed!
2. You only handle values of i up to 4*Lambda. When Lambda=2,
PoissonInverse(.999992,2) should be 10. Your function will return zero, because you will only test up to i=8.
What you need is a Do Until loop instead of your for loop.
3. It is good practice to declare the data types of ALL variables, including function arguments even if they are variants (the default).

FWIW, here's my version (without the error handling):

Option Explicit

Public Function InversePoisson(ByVal Prob As Double, ByVal Lambda As Double) As Long
Dim OldCumulative As Double, NewCumulative As Double, p As Double
Dim J As Long
Dim px As Double

If Prob = 0 Then
InversePoisson = 0
Exit Function
End If

px = Exp(-Lambda)

If Prob <= px Then
InversePoisson = 0
Exit Function
End If

NewCumulative = OldCumulative + Exp(-Lambda)

Do Until OldCumulative < Prob And Prob <= NewCumulative
OldCumulative = NewCumulative
J = J + 1
px = px * Lambda / J
NewCumulative = OldCumulative + px
Loop

InversePoisson = J

End Function

Brad

[MathGuy]

I don't know. I think you should check Brad's code for worms or viruses. You never know what kind of corporate secrets he's trying to steal from you. (Keep in mind that Brad Gile = Bill Gates. Pass it on.)

[Brad Gile]

I don't know. I think you should check Brad's code for worms or viruses. You never know what kind of corporate secrets he's trying to steal from you. (Keep in mind that Brad Gile = Bill Gates. Pass it on.)

ROFL!

Brad

[Skippy]

Aside from the use of cverr, you have other problems.

1. Rand is a keyword (the Rand() function). Keywords should NEVER be used as variable names. Excel should have barfed!

The function was quick and dirty to get the job done. rand isn't a keyword in VBA (as far as I know), so this didn't give rise to any problems.


2. You only handle values of i up to 4*Lambda. When Lambda=2,
PoissonInverse(.999992,2) should be 10. Your function will return zero, because you will only test up to i=8.
What you need is a Do Until loop instead of your for loop.


Only an actuary could care that a fucntion is wrong 8 times per million! 3 standard deviations above the mean was sufficiently close for my purposes.


3. It is good practice to declare the data types of ALL variables, including function arguments even if they are variants (the default).
FWIW, here's my version (without the error handling):


Thanks for your comments. I realise my coding was sub-optimal, but it was good enough for the job at hand.

The question I had was with returning an error - CVErr looks like the business!

[Brad Gile]

LOL!
Rand is a built in FUNCTION in Excel. It is what you use in the spreadsheet to generate random numbers there. The fact that it is not a built in VBA function or keyword is probably what saves you (VBA, like Visual Basic up through version 6, uses Rnd instead). Nevertheless, I still think its a bad idea to use Excel keywords/function names as variable names in VBA. You are just inviting Mr. Murphy.

As for "getting the job done", I won't argue unless someone else inherits your work and tries to do a different job using it - like Monte Carlo simulation, for example. If you want to write sloppy and incomplete code, that's cool. Just don't work for me. :D

Brad

[Skippy]

Ouch!!!