Forum Discussion
Serious bug with certain decimal numbers: Countifs and Roundup failed
In addition, Excel works by default with 15 digits and many functions ignore extra digits, but some work in background with 17 digits. More about that is here 17 or 15 digits of precision - Excel Tips - MrExcel Publishing
For this particular case if use
=SUMPRODUCT( ($C$1:$C$11<=C4)*($B$1:$B$11=B4))instead of COUNTIFS() it returns correct result.
Thank you, Hans.
It almost sorted the problem. But there were still a few numbers that it couldn't fix. As Joe mentioned, there seems to be some inherent problem in certain odd cases.
The solution that Sergei gave to use sumproduct in place of Countif fixed the problem.HansVogelaar wrote: ``Try using MROUND(..., 1/86400)``
I think that is ill-advised.
Consider Sheet1!B4. It display 8:49, but the actual value is 2/1/2020 8:49:48. (I write dates in the form MDY.)
=ISNUMBER(MATCH(MROUND(B4, 1/86400), B4, 0)) returns FALSE(!).
(FYI, we could write "0:0:1" with double-quotes instead 1/86400.)
The reason is: the binary result from MROUND does not match the binary representation of 2/1/2020 8:49:48. In fact, MROUND fails for more than 25% of the values in column B.
An exact binary match is important for lookups, for example.
In contrast....
=ISNUMBER(MATCH(INT(B4)+TEXT(B4,"h:m:s"), B4, 0)) returns TRUE for all values in column B.
-----
Caveat: INT(B4)+TEXT(B4,"h:m:s") works for constants that are accurate to the second.
And it works for calculated date and time that results in less than 23:59:59.500.
However, if date and time values might be accurate to the 1/10 second (or less), the following rounds to the second more reliably:
INT(B4)+TEXT(MOD(B4,1),"[h]:m:s.000")
The reason is: Excel rounds to the second when formatting to the hour, minute or second.
Consequently, for example, 2/1/2020 23:59:59.6 displays as 2/2/2020 0:00:00.
But INT(B4)+TEXT(B4,"h:m:s") would incorrectly result in 2/1/2020 0:00:00.
