Issue 26022 - Bug in POISSON() function
Summary: Bug in POISSON() function
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: OOo 1.1 RC5
Hardware: All All
: P3 Trivial (vote)
Target Milestone: ---
Assignee: oc
QA Contact: issues@sc
Depends on:
Blocks: 18704
  Show dependency tree
Reported: 2004-03-01 20:58 UTC by openminded
Modified: 2013-08-07 15:13 UTC (History)
3 users (show)

See Also:
Issue Type: PATCH
Latest Confirmation in: ---
Developer Difficulty: ---

proposed patch (692 bytes, patch)
2005-07-17 22:49 UTC, kyoshida
no flags Details | Diff
test program (2.87 KB, text/plain)
2005-07-17 22:53 UTC, kyoshida
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description openminded 2004-03-01 20:58:48 UTC
The function POISSON(Number;Mean;C) returns error #503 if "Number" is above a
certain number (which depends on the "Mean" parameter) when "C" is false. This
is most likely caused by a floating point overflow which shouldn't occur.


enter: =POISSON(150;120;false) in a cell in a spreadsheet. You will get an
error. At least I do. If you don't, try increasing the first parameter. The
correct return value for my example would be 0.0010114762. For other input
values the result would be different of course.

POISSON(148;120;false) works, but increasing the first parameter to 149 og above
triggers the error.

The same problem exists in MS Excel 2000 and the minimum values that cause an
overflow seems to be the same in Excel 2000 and OOO. I've heard that MS fixed
the problem in MS Office XP. The input values are perfectly legal and shouldn't
cause any errors to occur. I suspect an overflow occurs "internally" in the
function that calculates the return values.

Solution: If this is because of an overflow it would probably require a new
algorithm to calculate POISSON(). You could make it use higher precision floats
(with bigger exponents), but that would only be a temporary solution and an
overflow would still occur if the input values were higher.
Comment 1 frank 2004-03-02 11:25:21 UTC
Hi Eike,

is it your's or Niklas' ?

Comment 2 ooo 2004-03-02 12:36:19 UTC
This is just a special case of issue 18704.
You'll find implementation at sc/source/core/tool/interpr3.cxx method
ScInterpreter::ScPoissonDist(), code improvements would be welcomed, setting
needhelp keyword and changing target to not determined due to deadline constraints.
Comment 3 ooo 2004-08-04 10:44:05 UTC
Set PleaseHelp target.
Comment 4 kyoshida 2005-07-17 21:03:18 UTC
It all comes down to this (in python interactive shell):

>>> import math
>>> print math.pow( 120, 148 )
>>> print math.pow( 120, 149 )
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
OverflowError: math range error

In other words, a current binary float-point architecture can compute 120^148
but not 120^149, which is ultimately why POISSON(149;120;false) fails.

To work around this, we will need a new algorithm to compute a Poisson variable,
or use decimal float-point architecture (which is slower in performance, but
more accurate).

Comment 5 kyoshida 2005-07-17 22:27:50 UTC
I have a solution.  Let me prepare a patch, and a demo program.

Comment 6 kyoshida 2005-07-17 22:49:55 UTC
Created attachment 28003 [details]
proposed patch
Comment 7 kyoshida 2005-07-17 22:53:07 UTC
Created attachment 28004 [details]
test program
Comment 8 kyoshida 2005-07-17 23:00:43 UTC
Compile the test program by

  g++ -o poisson poisson.cpp

and run it with two arguments like

  ./poisson 150 120

You should then get an output like

  $ ./poisson 150 120
  old: inf
  new: 1.01147619891597513944e-03

Or, just run it with no arguments


and, if all goes well, you should see an output like this one

  $ ./poisson
  max difference: 9.02056207507939689094e-17
  number of non-equal results: 0

which tells you that the largest numerical difference between the old and new
algorithms for computing a poisson variable for x = 0 - 140; lambda = 0 - 140 is
9.020e-17, and all computed variables are within the binary float-points
rounding error (using rtl::math::approxEqual() function).

Comment 9 kyoshida 2005-07-17 23:10:56 UTC
The trick I did is this.

In the old algorithm, the poisson variable was computed as follows (in  pseudocode):

exp( -lambda ) * lambda^x / x!

But alas! lambda^x can overflow when lambda or x (or both) is sufficiently
large.  So I changed it to the following equivalent formula:

                 lambda   lambda         lambda
exp( -lambda ) * ------ * ------ * ... * ------
                    1        2              x

This yields an equivalent result within a binary float-point rounding error,
without a reduced risk of having an intermediate value that is large enough to

Comment 10 kyoshida 2005-07-17 23:12:46 UTC
A typo :P

'without a reduced risk' => 'with a reduced rick'
Comment 11 kyoshida 2005-07-17 23:22:25 UTC
changing the issue type to PATCH
Comment 12 kyoshida 2005-07-18 14:34:34 UTC
Setting the target milestone to 2.0.1.  I guess it's too late for 2.0, isn't it? ;)
Comment 13 ooo 2005-07-19 10:33:35 UTC
Hi Kohei,

Well done!

And yes, 2.0.1 is more appropriate.

Comment 14 ooo 2005-07-20 16:22:06 UTC
On branch cws_src680_dr37:
Comment 15 ooo 2005-08-11 13:30:15 UTC
Reassigning to QA.

re-open issue and reassign to
Comment 16 ooo 2005-08-11 13:30:24 UTC
reassign to
Comment 17 ooo 2005-08-11 13:30:27 UTC
reset resolution to FIXED
Comment 18 oc 2005-08-29 13:13:49 UTC
verified in internal build cws_dr37.
Remark: Fix is only for parameter "false". "True" will be handled by #i18704#  
Comment 19 oc 2005-11-01 11:23:11 UTC
closed because fix available in OOo2.0m136