#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!