3

There is a definition in POISSON function that:

#NUM! error – Occurs if either:

  • The given value of x is less than zero;
  • The given value of mean is less than zero.

But I try to do this in Excel 2013. It gave me differnt value. Here is my example:

=POISSON(0,-0.5,FALSE)

the result is: 1.648721271 instead of #NUM!

Any thoughts?

user3698799
  • 71
  • 7
  • I Get the same result in Excel 2016. Actually it only happens for `x = 0` and `mean < 0` – Pᴇʜ Apr 01 '19 at 06:59
  • 1
    It is a bug. The only answer to your question why is that programmers make mistakes, which sometimes slip through to production code. It is somewhat speculative to say anything more. – John Coleman Apr 01 '19 at 07:00
  • Still, it is fun to speculate (see below) and this question serves a good purpose of alerting others about a potentially serious bug. – John Coleman Apr 01 '19 at 16:55

1 Answers1

1

Speculatively, the bug might have come about as an optimization. Poisson(x,m,TRUE) is defined as e^(-m)*(m^x)/x!. One way to compute m^x when m is floating-point is as e^(x*Ln(m)). In a spreadsheet, you can observe that 

=POISSON(A1,A2,TRUE) - EXP(-A2)*EXP(A1*LN(A2))/FACT(A1)

always evaluates to exactly 0 whenever A1,A2 are in the correct domain (and not e.g. 0.0000000001 as might be the case if the calculation had used a different approach). Furthermore, EXP(-A2)*EXP(A1*LN(A2))/FACT(A1) fails when it should fail, giving #NUM! when fed 0, -0.5. My speculation is that the Excel programmers initially used a formula which failed when it should have failed, letting the called functions raise the error when appropriate. Then someone had the bright idea of just returning EXP(-mean) when x = 0 (since in that case the rest of the expression is 1 when it is defined at all). After all -- why bother to compute something when you know that it is 1?

What I find astonishing is that the bug is still there with POISSON.DIST Excel had been (and still is, although to a lesser extent) heavily criticized for the accuracy of its statistical functions and tests. So much so that "Friends don't let friends use Excel for statistics" is a relatively well-known saying among statisticians. See this for a discussion. The dotted statistical functions such as POISSON.DIST were explicitly designed to address the many complaints which had piled up. POISSON itself is just kept around for backwards compatibility. It is strange how this bug slipped through what should have been a thorough rewriting of these functions from the ground up.

John Coleman
  • 51,337
  • 7
  • 54
  • 119
  • Nice research. So still no test-driven development at Microsoft ^^ (or no test for the defined rules in the help file). – Pᴇʜ Apr 01 '19 at 13:21