https://bugs.documentfoundation.org/show_bug.cgi?id=174262
Bug ID: 174262
Summary: COUNTIF/COUNTIFS with ">"&number treats values as
equal that Excel counts as different
Product: LibreOffice
Version: 26.8.2.2 release
Hardware: All
OS: All
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Calc
Assignee: [email protected]
Reporter: [email protected]
Steps to reproduce:
1. A1: =1/3
2. C1: =A1+3*10^-16 (stored as 0.3333333333333336)
3. D1: =COUNTIF(C1,">"&A1) E1: =COUNTIF(C1,"="&A1) F1: =COUNTIF(C1,"<>"&A1)
Actual result (LibreOffice 26.8.2): D1 = 0, E1 = 1, F1 = 0
Expected result (Excel 365): D1 = 1, E1 = 0, F1 = 1
The criterion ">"&A1 is the text ">0.333333333333333" (15 significant digits)
in both programs. Excel appears to compare the cell rounded to 15 significant
digits (0.333333333333334) with that number, so the cell counts as greater;
LibreOffice compares with rtl::math::approxEqual, whose tolerance is wider, and
counts it as equal. The same happens below the criterion: C1 =
0.3333333333333323 gives Excel ">=" 0, "<" 1; LibreOffice ">=" 1, "<" 0.
In a sweep of 110 values just above and below 1/3, 2/7 and similar criteria,
LibreOffice 26.8.2 differed from Excel in 37 rows (all five operators >, >=, =,
<, <> checked). Note this is LibreOffice being looser than Excel near the 15th
digit, not stricter.
No patch yet.
--
You are receiving this mail because:
You are the assignee for the bug.
