Summary: | POISSON and POISSON.DIST functions | ||
---|---|---|---|
Product: | LibreOffice | Reporter: | raal <raal> |
Component: | Calc | Assignee: | Winfried Donkers <winfrieddonkers> |
Status: | RESOLVED FIXED | ||
Severity: | normal | CC: | erack, raal, winfrieddonkers |
Priority: | medium | ||
Version: | unspecified | ||
Hardware: | All | ||
OS: | All | ||
Whiteboard: | target:5.4.0 | ||
Crash report or crash signature: | Regression By: |
Description
raal
2016-12-31 13:41:20 UTC
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. |