# #NUM error on Poisson formula

• Hello, I´m having a strange problem using the Poisson Excel function.

On my 2002 SP3 Excel, I have the following problem:

=POISSON(131;220;1) -> 5.89866E-11
=POISSON(132;220;1) -> #NUM!

...after "132" all cells display the #NUM! error...

On a 2003 Excel, no problem whatsoever:

=POISSON(131;220;1) -> 5.89866E-11
=POISSON(132;220;1) -> 9.93948E-11
...etc...

How can I fix this error on my 2002 Excel..?

:thanx:

• You are running into numerical accuracy problems in either case with that large a mean for a Poisson. Excel isn't very careful in it's statistical functions in handling potential numerical accuracy problems. You may be better off switching to a normal approximation for large means. For your example, that would be
=NORMDIST(131+0.5,220,SQRT(220),1)

• Here is a UDF that should accurately calculate the cumulative Poisson for large values of the mean up to 700 or so.
[vba]Function MyCumPoisson(x As Integer, m As Double) As Double
Dim a As Double, i As Integer, s As Double
If x < 0 Then Exit Function
a = Exp(-m)
s = a
For i = 1 To x
a = a * m / i
s = s + a
Next i
MyCumPoisson = s
End Function[/vba]

• Upon further reflection here is a version that should handle any potential value for a Poisson mean.
[vba]
Function MyCumPoisson(x As Long, m As Double) As Double
Dim a As Double, i As Long, s As Double, spec As Boolean, mm As Double
If x < 0 Then Exit Function
spec = (m > 700)
If spec Then
a = 0
s = 0
mm = Log(m)
Else
a = Exp(-m)
s = a
End If
For i = 1 To x
If spec Then
a = a + mm - Log(i)
If m - a < 700 Then
a = Exp(a - m)
s = a
spec = False
End If
Else
a = a * m / i
s = s + a
End If
If s > 0.999999999 Then Exit For
Next i
MyCumPoisson = s
End Function
[/vba]

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!