https://bugs.documentfoundation.org/show_bug.cgi?id=90997
Bug ID: 90997
Summary: COUNTIF sometimes compares a cell as not being equal
to itself
Product: LibreOffice
Version: 4.2.7.2 release
Hardware: x86 (IA32)
OS: Linux (All)
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Calc
Assignee: libreoffice-bugs@lists.freedesktop.org
Reporter: dane.mas...@virgin.net
Created attachment 115244
--> https://bugs.documentfoundation.org/attachment.cgi?id=115244&action=edit
Spreadsheet that demonstrates the bug
The formulae
=COUNTIF(A1:A1,"<"&A1)
=COUNTIF(A1:A1,">"&A1)
sometimes return the value 1 while the formula
=COUNTIF(A1:A1,"="&A1)
sometimes returns the value 0.
Formulae like
=COUNTIF(A1:A1,">="&A1)
=COUNTIF(A1:A1,"<="&A1)
=COUNTIF(A1:A1,"<>"&A1)
similarly sometimes return incorrect values.
Clearly no one would actually want to make use of the above formulae, but the
bug also means that formulae like
=COUNTIF(A1:A40,">"&A5)
fail to return the correct answer. For example if A5 is the second largest
number in the range A1:A40, the above formula will usually return 1 (the
correct answer) but will occasionally return 2 because A5 has been counted as
being greater than itself.
When I looked at existing bug reports, it at first seemed to me that this might
be a duplicate of Bug 78447, but that was eventually closed as worksforme on
4.2.7.2, Linux whereas this bug definitely exists on 4.2.7.2, Linux. For the
record it also existed on 3.5.7.2, Linux.
I've attached a spreadsheet that demonstrates the bug.
A1:A230 are random numbers.
B1 uses COUNTIF to count the number of times that cells in the range A1:A1 are
less than A1 (the expected answer is of course 0). Similarly through to B230.
C1 uses COUNTIF to count the number of times that cells in the range A1:A1 are
equal to A1 (the expected answer is of course 1). Similarly through to C230.
D1 uses COUNTIF to count the number of times that cells in the range A1:A1 are
greater than A1 (the expected answer is of course 0). Similarly through to
B230.
B232, C232 and D232 are counts of the number of times the above uses of COUNTIF
produced the wrong answer. Occasionally all three counts will be zero. If so,
using F9 to force a recalculation usually produces non-zero values within one
or two iterations.
With some ingenuity it is possibile to work around the bug. For example
although
=COUNTIF(A1:A40,">"&A5)
will sometimes give the wrong answer
=COUNTIF(A1:A40,">"&A5)-COUNTIF(A5:A5,">"&A5)
will always give the correct one.
Finally I'll speculate that the bug might be caused by the use of an
intermediate variable with inappropriate precision, though I have to confess
that I would expect that to produce incorrect results more frequently than I
observe.
--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
