<html>
<head>
<base href="https://bugs.documentfoundation.org/">
</head>
<body><table border="1" cellspacing="0" cellpadding="8">
<tr>
<th>Bug ID</th>
<td><a class="bz_bug_link
bz_status_UNCONFIRMED "
title="UNCONFIRMED - Cumulative hypergeometric probability calculation error when discrete probabilities are too small"
href="https://bugs.documentfoundation.org/show_bug.cgi?id=117041">117041</a>
</td>
</tr>
<tr>
<th>Summary</th>
<td>Cumulative hypergeometric probability calculation error when discrete probabilities are too small
</td>
</tr>
<tr>
<th>Product</th>
<td>LibreOffice
</td>
</tr>
<tr>
<th>Version</th>
<td>5.4.5.1 release
</td>
</tr>
<tr>
<th>Hardware</th>
<td>x86-64 (AMD64)
</td>
</tr>
<tr>
<th>OS</th>
<td>Windows (All)
</td>
</tr>
<tr>
<th>Status</th>
<td>UNCONFIRMED
</td>
</tr>
<tr>
<th>Severity</th>
<td>normal
</td>
</tr>
<tr>
<th>Priority</th>
<td>medium
</td>
</tr>
<tr>
<th>Component</th>
<td>Calc
</td>
</tr>
<tr>
<th>Assignee</th>
<td>libreoffice-bugs@lists.freedesktop.org
</td>
</tr>
<tr>
<th>Reporter</th>
<td>charlesrv7@gmail.com
</td>
</tr></table>
<p>
<div>
<pre>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.
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
absurd.
Excel, R and hypergeometric calculators found in the Internet (for instance,
<a href="http://stattrek.com/online-calculator/hypergeometric.aspx">http://stattrek.com/online-calculator/hypergeometric.aspx</a>) give the correct
answers.
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
Additional Info:
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</pre>
</div>
</p>
<hr>
<span>You are receiving this mail because:</span>
<ul>
<li>You are the assignee for the bug.</li>
</ul>
</body>
</html>