# [Libreoffice-bugs] [Bug 117041] New: Cumulative hypergeometric probability calculation error when discrete probabilities are too small

`https://bugs.documentfoundation.org/show_bug.cgi?id=117041`
```
Bug ID: 117041
Summary: Cumulative hypergeometric probability calculation
error when discrete probabilities are too small
Product: LibreOffice
Version: 5.4.5.1 release
Hardware: x86-64 (AMD64)
OS: Windows (All)
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Calc
Assignee: libreoffice-bugs@lists.freedesktop.org
Reporter: charles...@gmail.com

Description:
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.

Example:
HYPGEOM.DIST(34,36,89,100,1) is 1.78862E+20 in Calc version 5.4.5.1 (x64). That
is absurd, as probabilities are limited to 1.

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
absurd.

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
"HYPGEOM.DIST(53,55,269,300,1)".

Actual Results:
The incorrect results will be, respectively, "1.78862483604230E+20" and
"0.991282957036719".

Expected Results:
The correct results should be, respectively, "0.95624768719555" and
"0.988500551352757".

Reproducible: Always

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
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
```