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.
That's very interesting,
So the essence of this rounding issue seems to be for numbers like x=43863.89142,
=COUNTIF(x,x)
=> 1
as expected, but
=COUNTIF(x,"="&x)
=> 0 ?!
A safer approach is to convert date/times to integers for comparisons by multiplying by 24*60*60.
Thank you for the solution. Especially since Sumproduct doesn't spill, for DA this seems to be the best work around.
On a side note, interestingly this problem arises only if the integer part is above 1024. If I try 1023.89142, it seems to work very well. But the moment it crosses 2^10, there is some problem. And again it is happening for 0.89142 but there is no problem for 0.89143 or 0.89141The 1024 threshold looks to be significant, a recent MS Research podcast stated that the original C++ code predated IEEE-754 double precision standard, some parts still using an old 40-point format (see https://en.wikipedia.org/wiki/Microsoft_Binary_Format )
Since technical documentation is lacking, I had collected together some findings on numeric formats here, one being 'for accurate timing calculations serial numbers should be scaled by the number of milliseconds per day'.
As I remember such behaviour is for all <DO>IF() functions, e.g. =SUMIF(x,"="&x, x) =>0 vs
=SUMIF(x,x,x) =>x
Yes, and in general any functions or operators that take text type inputs will be similarly affected since expressions such as ""&x get incorrectly rounded. The SUMPRODUCT workaround you suggest takes numeric inputs and so is not affected.
