Apache OpenOffice (AOO) Bugzilla – Full Text Issue Listing 
Summary:  Bug in POISSON() function  

Product:  Calc  Reporter:  openminded <fluffe>  
Component:  code  Assignee:  oc  
Status:  CLOSED FIXED  QA Contact:  issues@sc <issues>  
Severity:  Trivial  
Priority:  P3  CC:  issues, khirano, kyoshida  
Version:  OOo 1.1 RC5  
Target Milestone:    
Hardware:  All  
OS:  All  
Issue Type:  PATCH  Latest Confirmation in:    
Developer Difficulty:    
Issue Depends on:  
Issue Blocks:  18704  
Attachments: 

Description
openminded
20040301 20:58:48 UTC
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 floatpoint 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 floatpoint 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.01147619891597513944e03 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.02056207507939689094e17 number of nonequal 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.020e17, and all computed variables are within the binary floatpoints 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 floatpoint 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. reopen 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 