Bug ID: 117041
Summary: Cumulative hypergeometric probability calculation
error when discrete probabilities are too small
Version: 184.108.40.206 release
Hardware: x86-64 (AMD64)
OS: Windows (All)
HYPGEOM.DIST(x,NSample,Successes,Npopulation,Cumulative) function fails to
calculate cumulative probability (Cumulative=1) for situations where part of
the individual mass functions are too small and gives out "Err:502". What the
function should do is to equate those values to zero or do not consider them
when adding Pr(X=0) + Pr(X=1) + ... + Pr(X=x) to calculate Pr(X<=x).
That is what, for instance, MS Excel 2013 seems to do when calculating
cumulative hypergeometric probabilities. Its corresponding function
(HYPGEOM.DIST) does not give out wrong answers in situations where Calc does.
HYPGEOM.DIST(34,36,89,100,1) is 1.78862E+20 in Calc version 220.127.116.11 (x64). That
is absurd, as probabilities are limited to 1.
The correct answer is 0.9562476872.
There are countless input values that will give out erros too. For instance,
HYPGEOM.DIST(53,55,269,300,1) in Calc is 0.991282957, but the more correct
answer is 0.988500551. Such kind of erro is worse, because it is not patently
Excel, R and hypergeometric calculators found in the Internet (for instance,
http://stattrek.com/online-calculator/hypergeometric.aspx) give the correct
It seems just discarding the individual mass probabilities for values of
Pr(X=x) where the results are so small they fall beyond (or bellow) machine
precision is enough to prevent the error.
I did that using the individual hypergeometric mass probability function from
Calc (either HYPGEOMDIST or HYPGEOM.DIST with "Cumulative=0") and the error is
avoided (see the "Aux" sheet in the .ods file I am sending attached).
Steps to Reproduce:
In any Calc cell enter, for instance, "=HYPGEOM.DIST(34,36,89,100,1)" or
The incorrect results will be, respectively, "1.78862483604230E+20" and
The correct results should be, respectively, "0.95624768719555" and
User Profile Reset: No
All I did seems to point to an easy solution to have the bug fixed: just equate
to zero or do not take into consideration the values of Pr(X=x) in error (value
too small, bellow machine precision).
I only tested in Windows 10 environment. I do not know if the error in
HYPGEOM.DIST also occurs in Linux environments.
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36
(KHTML, like Gecko) Chrome/65.0.3325.181 Safari/537.36
You are receiving this mail because:
You are the assignee for the bug.
Libreoffice-bugs mailing list