Description: POISSON spec *Syntax:* POISSON( /Integer/ x ; /Number/ l [ ; /Logical/ Cumulative = TRUE() ] ) *Returns:* Number *Constraints:* l > 0, x >= 0 Constraint for l is not applied correctly. POISSON and POISSON.DIST are identical as far as the function code is concerned. When POISSON.DIST is saved, or when POISSON is saved to xls/xlsx, it should be saved with 3 arguments (adding the cumulative argument if not entered in Calc) to achieve interoperability. Syntax POISSON.DIST(x,mean,cumulative) Cumulative Required. But in Calc formula =POISSON.DIST(150,120) works. Should not work, because third parameter is not present. Actual Results: . Expected Results: . Reproducible: Always User Profile Reset: No Additional Info: User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:50.0) Gecko/20100101 Firefox/50.0
POISSON(n,0,0), POISSON.DIST(n,0,0), POISSON(n,0,1), POISSON.DIST(n,0,1) all return 1 in Excel. This is an Excel bug. Firstly, the mathematical definition of Poisson distribution states that lambda must be > 0. Secondly, the mathematical calculation ( lambda^k * e^-lambda / k! ) has 0 as result when a lambda value of 0 is applied. In ODFF, lambda must be greater than 0. However, this leads to a dilemma: -the current code for POISSON(.DIST) has fixed result for lambda==0: 0 for non cumulative and 1 for cumulative. The 0 I can explain, the 1 I can't explain. -when adding the constraint lambda>0, the above code can be removed. -Without the above code, the implementation of the mathematical calculation produces 1 as result in case of lambda being 0. So when I apply the constraint for lambda for POISSON and not for POISSON.DIST, the function returns the same result as Excel in case of lambda==0. That result is mathematically incorrect. I intend to apply the constraint lambda>0 for both POISSON and POISSON.DIST. This may be different than the Excel definition, but it is mathematically correct and I don't want to copy an Excel bug. @Eike, your view is highly appreciated.
I don't see any reason why or how lambda could be 0 in a poisson distribution. However *if* someone assumed that 0 (events per interval) would be valid then it would be logical that the result for non-cumulative would be 0 as well, as the event would never happen for any probability.. or so my deducing ;-) But if it's 0 for any k then it should also be 0 in the cumulative case because any integral between any points would be 0 as well.. Let's stick with the correct constraints and not allow lambda 0.
Winfried Donkers committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=482db19e3c792892c536898408dd7da98265073a tdf#105019 fix POISSON/POISSON.DIST deficiencies. It will be available in 5.4.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.