Apache OpenOffice (AOO) Bugzilla – Issue 26022
Bug in POISSON() function
Last modified: 2013-08-07 15:13:53 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. Example: 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.
Hi Eike, is it your's or Niklas' ? Frank
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.
Set PleaseHelp target.
It all comes down to this (in python interactive shell): >>> import math >>> print math.pow( 120, 148 ) 5.23388788088e+307 >>> 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). Kohei
I have a solution. Let me prepare a patch, and a demo program. Kohei
Created attachment 28003 [details] proposed patch
Created attachment 28004 [details] test program
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 ./poisson 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). Kohei
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 overflow. Kohei
A typo :P 'without a reduced risk' => 'with a reduced rick'
changing the issue type to PATCH
Setting the target milestone to 2.0.1. I guess it's too late for 2.0, isn't it? ;)
Hi Kohei, Well done! And yes, 2.0.1 is more appropriate. Thanks Eike
On branch cws_src680_dr37: sc/source/core/tool/interpr3.cxx 1.12.136.1
Reassigning to QA. re-open issue and reassign to oc@openoffice.org
reassign to oc@openoffice.org
reset resolution to FIXED
verified in internal build cws_dr37. Remark: Fix is only for parameter "false". "True" will be handled by #i18704#
closed because fix available in OOo2.0m136