In Version: 25.8.2.2 (X86_64)
Build ID: d401f2107ccab8f924a8e2df40f573aab7605b6f
CPU threads: 24; OS: Windows 11 X86_64 (build 26200); UI render: Skia/Vulkan; VCL: win
Locale: de-DE (en_US); UI: en-US
Calc: CL threaded

the "Using floating point fails" section works for me (shows 1, as expected).

For the "Using floating point with Time-Format fails" section, it indeed shows 0. However:

1. I can't understand what "Seems to happen if the item is at the end, with a slightly larger area, it works" means specifically. I tried to change the formula in C11 to =COUNTIFS($A$7:$A$12;"="&$A11), and it fails likewise. The formula in C12 is comparing an empty cell to an empty cell, so is unrelated.

2. It is not actually about "being at the end" - it seems specific to the concrete time value of '01:26,47' - which fails, wherever in the range it is (e.g., extending the formula in C11 to C7:C11, and copying the value of '01:26,47' to A9, makes C9 also show 0).

3. Even changing number format of A11 to Standard doesn't change the recalculated result of C11.

4. It seems to be very unusual. The following formula also shows FALSE:

=A11=VALUE(TEXT(A11;"Standard"))

and the following formula shows -2,31481481481481E-14:

=A11-VALUE(TEXT(A11;"Standard"))

Note that formatting A11 as Standard shows this number:

0,00100081018518519

and a formula like =TEXT(A11;"Standard") shows this text:

0,00100081018518519

both look identical, and - what's important - have 17 digits after decimal; so the reported difference of 2E-14 is completely unexpected.

(In reply to Mike Kaganski from comment #1)
> =A11=VALUE(TEXT(A11;"Standard"))

A note: the "Standard" string is unfortunately locale-specific; it may need to be "General", or whatever the format is called in the specific locale.

Hmm.

The problem with inequality itself seems NOTABUG, and related to the usual floating-point calculation imprecision [1]. Specifically:

1. Entering '01:26,47' time converts to 0,0010008101851851851;
2. In accordance to widely accepted policy of spreadsheets, when showing the value, only 15 significands are shown: 0,00100081018518519;
3. The same number of significands is used to convert to strings, so the '"="&A11' produces a Criterion "=0,00100081018518519";
4. When converting the "0,00100081018518519" to a number, the result is 0.0010008101851851901;
5. the comparison of these values in steps 1 and 4 produces false, even with our normal approximate equality (the difference is ~5E-18, which is greater than ~1E-18, which is the threshold used for approximate equality, calculated as the original values * 1E-15).

I am not sure if we can improve this specific corner case - the usual "for any floating-point calculations, use rounding" seems not fitting this well, but I don't see a robust way out here. Increase threshold to original value * 5E-15 ? I know for sure, that I tested 1E-14, and it was problematic; but I didn't test 5E-15.

===

However, the problem with subtraction, noticed in comment 1, is separate and different. The formula

=A11-VALUE(TEXT(A11;"Standard"))

indeed produces -2,31481481481481E-14; whereas the formula

=RAWSUBTRACT(A1;VALUE(TEXT(A1;"General")))

produces the expected -4,98732999343332E-18.

Needs more investigation.

[1] https://wiki.documentfoundation.org/Faq/Calc/Accuracy

The difference problem is likely related to the changes introduced in tdf#127334. Indeed, in v.7.6, the difference gives the expected ~5E-18, while in v.24.2, it is ~2E-14.

A note: to avoid the locale-specific nature of TEXT function, the testing formulas can be

=A1-VALUE(CONCATENATE(A1))
=VALUE(A1)-VALUE(CONCATENATE(A1))
=RAWSUBTRACT(A1;VALUE(CONCATENATE(A1)))

(In reply to Mike Kaganski from comment #3)
> 5. the comparison of these values in steps 1 and 4 produces false, even with
> our normal approximate equality (the difference is ~5E-18, which is greater
> than ~1E-18, which is the threshold used for approximate equality,
> calculated as the original values * 1E-15).
> 
> I am not sure if we can improve this specific corner case - the usual "for
> any floating-point calculations, use rounding" seems not fitting this well,
> but I don't see a robust way out here. Increase threshold to original value
> * 5E-15 ? I know for sure, that I tested 1E-14, and it was problematic; but
> I didn't test 5E-15.

FTR: the threshold is calculated using 0x1p-48 [1], which is ~3.5E-15; so it is greater than 1E-15, but less than 5E-15, which would be necessary for this case.

Excel 2016 gives the expected 0 for the difference.

[1] https://opengrok.libreoffice.org/xref/core/sal/rtl/math.cxx?r=c5c8e28959cc379452b15aa250ca7151a9e3d370&mo=23757&fi=604#604

Created attachment 203125 [details]
Update reproducer showing which cases result in incorrect "0" results from COUNTIFS

Thanks for the discussion, in the reproducer, the reproducer was slightly wrong, the updated content now comes out as

Time-Formatted:
0	=COUNTIFS($A$7:$A$11;"="&$A11)
0	'=COUNTIFS($A$7:$A$12;"="&$A11)

Decimal-Formatted:
0	'=COUNTIFS($A$17:$A$21;"="&$A21)
1	'=COUNTIFS($A$17:$A$22;"="&$A21)


Whereas processing in Excel results in "1" for all of them.


Having an "equals" fail due to precision is strange, are there two different ways of transforming the data to string before comparing?


I have updated the attachment with the updated content.

(In reply to Dominik Stadler from comment #6)
> Thanks for the discussion, in the reproducer, the reproducer was slightly
> wrong, the updated content now comes out as
> 
> Time-Formatted:
> 0	=COUNTIFS($A$7:$A$11;"="&$A11)
> 0	'=COUNTIFS($A$7:$A$12;"="&$A11)
> 
> Decimal-Formatted:
> 0	'=COUNTIFS($A$17:$A$21;"="&$A21)
> 1	'=COUNTIFS($A$17:$A$22;"="&$A21)
> 
> 
> Whereas processing in Excel results in "1" for all of them.

I still see
 Decimal-Formatted:
 1	'=COUNTIFS($A$17:$A$21;"="&$A21)
 1	'=COUNTIFS($A$17:$A$22;"="&$A21)

and that may be because when you copy the date from A11, and paste to A21, it keeps the exact value; but when it goes through save-and-reload, it is rounded to 0,00100081018518519.

> Having an "equals" fail due to precision is strange, are there two different
> ways of transforming the data to string before comparing?

Not quite understand the question. Which two ways are meant? We only discussed one way of transforming the data to string.

Yes, saving, editing or changing the precision sometimes affects this, although a simple safe-and-reload did not change it for me, adjusting precision of the cell-formats did. For time-format I did not find a way to make it work.

With "two ways" I meant that the cell is compared with itself as part of COUNTIFS() and somehow this comparison ends up being "not equal", which indicates that the value is handled in two different ways, otherwise "precision" or "conversion to text" couldn't have an impact.

(In reply to Dominik Stadler from comment #8)
> With "two ways" I meant that the cell is compared with itself as part of
> COUNTIFS() and somehow this comparison ends up being "not equal", which
> indicates that the value is handled in two different ways, otherwise
> "precision" or "conversion to text" couldn't have an impact.

When you use a comparison like '=A11=A10', the comparison doesn't use any conversion to strings. But when you use COUNTIFS with a Criterion like "="&A11, that criterion is a string - and A11 gets converted to string automatically there. The resulting criterion is a string "=0,00100081018518519", which has a string which is *not* a precise representation of the value in A11, but rounded to 15 significands. The *precise* criterion would be "=0,001000810185185185", but there is no method in Calc to convert the number to string with that precision. Then the criterion is evaluated, and the string "0,00100081018518519" gets converted to number again - to perform comparison to the actual cell value (numerical). Since cell A11 contains 0,001000810185185185, and the string "0,00100081018518519" gets converted to number 0,00100081018518519 - their comparison gives false.

Of course, in this specific case, it's unreasonable to use the string as criterion: you could rewrite your formula to be simply

  =COUNTIFS($A$7:$A$12; $A11)

and avoid the problem completely: if you only need an equality, it's better, more robust, and also faster to avoid that "convert to string, then convert back to number" dance. But if you would need a criterion like "<>"&$A11, that problem would appear again, so that is not a solution to this bug.

Thanks for the workaround of avoiding strings for equals-condition, it helps in one case. 

But in another place we are using "<" and as expected that has similar issues:

01:21,44	1	=COUNTIFS($A$1:$A$15;"<"&$A1)	
01:28,99	4	=COUNTIFS($A$1:$A$15;"<"&$A2)	
01:03,19	0	=COUNTIFS($A$1:$A$15;"<"&$A3)	
01:27,99	3	=COUNTIFS($A$1:$A$15;"<"&$A4)	<= Expecting „2“, but getting "3"!
01:26,47	3	=COUNTIFS($A$1:$A$15;"<"&$A5)

https://gerrit.libreoffice.org/c/core/+/191883

Mike Kaganski committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/d32c664c3df302b7107a70858aa55b11601225cf

tdf#168673: approx equality threshold no less than 1/2 15th significand

It will be available in 26.2.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.

I verified via the daily Linux-rpm_deb-x86_64@tb99-TDF2025-10-06_17.27.27/ that the fix works. After editing the formulas in the reproducer-file, the computation is now correct in all cases.

Thanks for the quick fix!

Unfortunately I cannot test the fix but I played a little bit with the example file using Version: 24.2.7.2 (X86_64) / LibreOffice Community
Build ID: ee3885777aa7032db5a9b65deec9457448a91162
CPU threads: 4; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: Skia/Raster; VCL: win
Locale: de-DE (de_DE); UI: de-DE
Calc: threaded

As a result I think I discovered three independent problems: Some strange behavior when loading the document, a problem with the default representation of numbers and one with the specification of the equality operator.

When I open Reproducer_LibreOffice_COUNTIF_Equals_FloatingPoint.ods then Cell C21 shows 0 and C22 shows 1. When I select Cell C21 and press F9 (Recalc) then the correct value 1 is shown. This behavior has nothing to do with any properties of the equality operator and this is probably not fixed.

For every floating point number (except a few special cases such as NaNs) there is a string representation that allows to reconstruct this number exactly. LibreOffice seems not use this kind of representation by default. As a result you may loose information whenever you use a round trip from floating point to string representation back to floating point. This may affect operations such as storing and reloading documents.

As the fix affects some very basic functionality I had a look into the specification of the equality operator. Section 6.4.7 Infix Operator "="  [1] says:

“Evaluators may approximate and test equality of two numeric values with an accuracy of the magnitude of the given values scaled by the number of available bits in the mantissa, ignoring some least significant bits and thus providing compensation for not exactly representable values.”

A few questions came into my mind:

1) Does the fix conform to this specification? The specification allows to ignore a few bits but does not mention the use of more complex algorithms. On the other hand the specification seems not to require that the “=”-Operator has the mathematical properties of an equivalence relation.

2) Does the fix guarantee the transitivity requirement of equivalence relations? Can you exclude the possibility that three floating point numbers A, B and C exist such that the implemented algorithm says A is equal to B, and B is equal to C but A is not equal to C? 

3) Is the reported behavior a bug? Maybe there are expectations that Calc as it is specified cannot meet. The specification allows to ignore some least significant bits. How many? That is left to the implementation. This means that the behavior of the equality operator and thus the behavior of many other functions that depend on it may differ between implementations. If you want reliable results you probably should not use the equality operator or any functionality that depends on it. But what is the alternative?

4) What happens when you have two floating point numbers A and B that differ only in the bits that the equality operator chooses to ignore? Does this imply you have A = B but A - B is not equal to zero?

5) How are the “<” and “>” operators related to equality? I did not find any consistency requirements in the specification of these operators (Section 7.4.9, below the specification of the Operator “=”). Thus maybe these operators use all bits in the mantissa. A consequence would be: If two number A and B differ only in the ignored bits you may have A < B and also A = B.

6) Is NOT (A < B) the same as A >= B? Maybe not. The first expression may compare all mantissa bits but the second may ignore some least significant bits. And of course this behavior may be implementation dependent.

So I think the comparison operators are specified to show some pretty strange behavior that you cannot fix by improving the implementation. And I did not even touch the peculiarities of date and time arithmetic… Thus additional questions surface:

How do you explain these things to the user, e.g. in the help information? What are the recommendations for getting reliable and reproducible results that you can exchange with other tools such as Excel?
and 
Why not simply use a floating point arithmetic that conforms to the IEEE 754 – Standard as probably most programming languages do?

------
[1] https://oasis-tcs.github.io/odf-tc/odf1.4/part4-formula/OpenDocument-v1.4-part4-formula.html#__RefHeading__1018024_715980110

(In reply to Albrecht Müller from comment #14)
> 1) Does the fix conform to this specification?

Yes.

> 2) Does the fix guarantee the transitivity requirement of equivalence
> relations?

No.

> 3) Is the reported behavior a bug?

Yes.

> 4) What happens when you have two floating point numbers A and B that differ
> only in the bits that the equality operator chooses to ignore? Does this
> imply you have A = B but A - B is not equal to zero?

No. But if you replace "-" with "RAWSUBTRACT", then yes.

(In reply to Mike Kaganski from comment #15)
> No. But if you replace "-" with "RAWSUBTRACT", then yes.

Thanks for pointing to RAWSUBTRACT. Are there other functions that offer IEEE 754 conforming functionality such as equality and comparison operators?

(In reply to Mike Kaganski from comment #15)

> > 2) Does the fix guarantee the transitivity requirement of equivalence
> > relations?
> 
> No.
> 

Expected behavior: “=” is transitive
Actual behavior: “=” is not transitive

A new bug?

(In reply to Albrecht Müller from comment #17)
> Expected behavior: “=” is transitive

The expectation is wrong.

(In reply to Mike Kaganski from comment #18)
> > Expected behavior: “=” is transitive
> 
> The expectation is wrong.


From https://en.wikipedia.org/wiki/Equals_sign:

“The equals sign (British English) or equal sign (American English), also known as the equality sign, is the mathematical symbol =, which is used to indicate equality.” 

From https://en.wikipedia.org/wiki/Equality_(mathematics):

“Basic properties about equality like reflexivity, symmetry, and transitivity have been understood intuitively since at least the ancient Greeks, but were not symbolically stated as general properties of relations until the late 19th century by Giuseppe Peano.”

Please:

1. Realize that Calc is not an algebra system;
2. Realize that "Equality" and "Equality (mathematics)" is different; and even that "mathematics" is broad, including very different notions of "equality", including non-transitive variants;
3. Stop spamming in this issue with unrelated posts. The situation you try to discuss here is not related to the issue here, and in not new. If needed, file a separate bug.

There is a simple workaround that fixes the wrong result of the COUNTIFS expression:

The COUNTIFS expression refers to a range that contains data formatted as time values. Change the format of this range to standard floating point. You should see the same values as in the corresponding part below. This does not change the result of the COUNTIFS expression. Save the file, reload it and trigger a recalculation, e.g. by pressing Cntrl+Shift+F9. Now the cell that contains the COUNTIFS expression should show the correct result. You can do the reverse: Assign the same time format again, save the spreadsheet, reload and recalculate. You should see the wrong result as before.

How I assume this works: Changing the format of the cells does not change the double values contained in the cell. Thus the result of the COUNTIFS expression does not change. If you save the spreadsheet the floating point values are stored with a 15 decimal digit precision which is not enough to store the exact double values contained in the cells. So when the spreadsheet is loaded again the values in the search range get changed slightly in a way that they can be represented exactly by a decimal representation that uses 15 digits. Thus the value given in the search criterion can match the corresponding value in the searched range. Calc seems to store calculated values and to display these values instead of recalculating them after loading the document. Thus it is necessary to manually trigger a recalculation.

It is arguably a bug when saving and reloading changes the content of a document. However, in this case you can use this bug to work around the problem that we discuss here. Other workarounds are possible: In case you want to compare for equality you can give a reference to the cell that contains the value searched for. In case that you need another comparison operator you could construct a decimal representation that is precise enough to denote the exact double value you need for the criterion. I tried it. The construction is ugly but in a “proof of concept” example I could make it work because converting a decimal representation containing more than 15 digits to a double value seems to use the additional information correctly.

The main result is: COUNTIFS is an example of a group of functions which can use a string that specifies some kind of a search criterion. These functions have only partial support of scenarios like this where the value needed in the search criterion cannot be represented by a 15 digit decimal representation.

An analysis of this issue shows that this use case involves at least four different equivalence relations:
1) The equivalence relation defined by the IEEE 754 standard for double values. This is the basis for relation #2.
2) The equivalence relation defined by the Calc’s equality predicate which is essentially the same as relation #1 except that it ignores a fixed number of low order bits.
3) The equivalence relation where values are equal if and only if they have the same canonical 15 digit decimal representation.
4) The equivalence relation where values are equal if and only if they have the same canonical date and time representation. This actually is a set of equivalence relations as Calc has the switch “Precision as shown” [1]. If this switch is set the meaning of equality depends on the formatting of the values. I think the semantics of this switch is not sufficiently defined in the case when left and right operand are formatted using different precision. Which precision should be used in this case?
I use the term “canonical” in the sense that different representations of the same number should considered identical.

In our use case the COUNTIFS function is probably expected to use equivalence relation #4 as the arguments are formatted as time values, but the reference value is chosen according relation #3 as it is given by a 15 digit decimal representation, and the actual comparison is done according to equivalence relation #2.

This is prone to go wrong! Fixing this issue would require to tidy up this mess. I see no way how a small change of the identity operator can do the trick. Therefore I reopened the bug. I see the following options for dealing with this issue:

1) Do nothing, close it as WONT_FIX. Reasons:

Changing the identity operator in the way you implemented will not only affect the spreadsheets that contain the COUNTIFS operator but may change the results of any spreadsheet that somehow uses the equality operator. There are millions of users who may have used equality in their spreadsheets. Thus doing nothing may save a good part of them the trouble to mull over some strange pseudo-random alterations in their spreadsheets.

Doing nothing will also prevent Calc deteriorating. I did a superficial search for non-transitive equality and found that programming languages like PHP and JavaScript have non-transitive equality operators. The consensus seemed to be that having non-transitive equality operators is a bad idea because they make it difficult for users to understand what they are doing. Getting floating point calculations right is hard enough. There is no need for features that cause extra problems.

2) To improve Calc, someone familiar with this issue could write some help information explaining the limitations of the functions in question. That would allow users to assess what they can expect from these functions and what they can’t.

3) Actually cleaning up is also an option. But I fear that this will require too much work.
----
[1] See help information https://help.libreoffice.org/latest/en-US/text/shared/optionen/01060500.html#hd_id3145150

Created attachment 203343 [details]
Wrong results of COUNTIFS function depending on the “precision as shown” switch

I was wrong when I assumed the changes to the equality operator will deteriorate LibreOffice because they break transitivity. I discovered that the equality operator in the version I use is already non-transitive. Thus the fix does not change anything in this respect. Sorry for that. The RAWSUBTRACT function was quite useful in this analysis.

Using the attached file I can switch between a right and a wrong result of the COUNTIFS function by toggling the “precision as shown” option.

Mike Kaganski committed a patch related to this issue.
It has been pushed to "libreoffice-25-8":

https://git.libreoffice.org/core/commit/05d1d307e83431f2e1e649ffe3c1f0854b44d13a

tdf#168673: approx equality threshold no less than 1/2 15th significand

It will be available in 25.8.3.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.

Mike Kaganski committed a patch related to this issue.
It has been pushed to "libreoffice-25-2":

https://git.libreoffice.org/core/commit/11c99476fd5af7fafaabd783ddfc7e9841b43db7

tdf#168673: approx equality threshold no less than 1/2 15th significand

It will be available in 25.2.8.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.

This comment concludes my analysis of this bug. As a result I think that the problem is due to missing, wrong and/or unclear specifications and therefore cannot be easily fixed with a few implementation tricks. Reporting all the strange things I encountered during my analysis as separate, unrelated bugs probably is also unlikely to help. Since I do not expect the situation to improve, I will describe some workarounds. Details:

The specification defines the equality operator in terms of the binary representation of double values (see comment #14). Mike Kaganski mentions in comment #3 that the precision of floating point representations is usually limited to 15 decimal digits. The reason for this limitation is probably that it guarantees lossless round-trip conversion from decimal representation to double value back to decimal representation. Therefore, from a user’s point of view Calc’s arithmetic mostly behaves in a way as if it were a 15 digit decimal arithmetic. As users cannot see the binary representation their concept of equality is based on this decimal representation. Thus we have two different concepts of equality: One based on the binary, the other based on the one decimal representation. The important point is that these concepts differ in terms of which values are considered equal. The result of counting values that occur in a specific cell range depends on which values are counted and thus on which equality concept you use. As the user’s view of equality differs from what the equality operator actually does you have to expect wrong counts.

The situation is different if you consider date and time representations. I was not able to identify a concept of equality that is easy to understand and that respects the restrictions imposed by the underlying binary representation. Just one example: The behavior of the user interface suggests that timestamps count ticks with a length of one second, while durations use 100 ns ticks. So it is possible to add 00:00:00,0000001 and 1899-12-30 00:00:00. Is the result the same as 1899-12-30 00:00:00 or is it different? If users cannot know which date and time values are considered equal, how can they expect that the COUNTIFS function will return correct results?

In order to improve this situation I think the following actions are necessary before actually changing the implementation:
- Some kind of specification should be written that clarifies how the date and time arithmetic works and its relation to floating point arithmetic. This should include a definition of equality that is compatible with date and time representation.
- The specification of the equality operator should require that this operator respects the data types of its arguments and uses the corresponding concept of equality. These concepts should be based on properties that users can understand and not on properties of the internal binary representation.
- It should be specified how the COUNTIFS function (and some other related functions) handle date and time representations in their criteria strings. This would enable them to use the correct data types in equality tests.
- Assessing the impact of resulting changes on existing spreadsheets and on other relevant areas, e.g. ODF specification, UNO (e.g. data arrays, structs related to date and time), StarBasic, compatibility with Excel, impact on financial functions, “precision as shown” feature, etc.

Based on the course of the discussion, I assume that I cannot expect such actions in the foreseeable future. Therefore I think I have to consider the results of the equality operator and thus of the COUNTIFS function as essentially undefined. Perhaps I can assume that the equality operator always will treat values with an identical binary representation as equal and never will treat them as equal if they differ sufficiently. Anything in between can change without notice, so I cannot rely on it. The way this bug report is handled illustrates the problem. To obtain reliable results, I must therefore limit the use of the equality operator and the COUNTIFS function to situations where equal values are guaranteed to have identical binary representations and different values are not too close to each other. In Dominik Stadler’s example I would create a range that contains the original values multiplied by the number of hundredths of a second per day and rounded to whole numbers before counting. This would map the original values to not-too-big integer values which I think is safe. This type of workaround can also help avoid using the “precision as shown” feature.

Mike Kaganski committed a patch related to this issue.
It has been pushed to "libreoffice-25-2-7":

https://git.libreoffice.org/core/commit/73408848a7ebc7594006346e90d2b6ee0650246b

tdf#168673: approx equality threshold no less than 1/2 15th significand

It will be available in 25.2.7.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.