PDA

View Full Version : Inverse Poisson in Excel

Skippy
11-22-2002, 12:06 AM
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 &lt; Fx Then
InversePoisson = 0
Exit Function
End If
For i = 1 To lambda * 4

px = px * lambda / i
Fx = Fx + px
If rand &lt; 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
11-22-2002, 10:06 AM
Check out CVErr in VBA help.

11-22-2002, 01:16 PM
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 &lt;= px Then
InversePoisson = 0
Exit Function
End If

NewCumulative = OldCumulative + Exp(-Lambda)

Do Until OldCumulative &lt; Prob And Prob &lt;= NewCumulative
OldCumulative = NewCumulative
J = J + 1
px = px * Lambda / J
NewCumulative = OldCumulative + px
Loop

InversePoisson = J

End Function

MathGuy
11-22-2002, 02:06 PM
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.)

11-23-2002, 10:30 PM
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!

Skippy
11-24-2002, 11:22 PM
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!