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 for a detailed response, JoeUser2004 and thank you for the alternative solution SergeiBaklan
I had a list of end users with various entry times and I had to count how many times an user has done entry up to a given point. So, I wanted to apply this: =Countifs([Time],"<="&[@Time],[User],[@User])
That is when I noticed that for some of the entries I was getting wrong answers. Roundup was more of a workaround I attempted to fix the problem.
I didn't realise this issue existed in MS Excel. Thank you for helping me understand the bug.
Viz wrote: ``thank you for the alternative solution SergeiBaklan``
I agree with SergeiBaklan's SUMPRODUCT alternative to solve your problem with COUNTIFS. I suggest that you mark his response as "best" or "answer".
Ironically, you were correct in the first place: the original problem with COUNTIFS is indeed related the formatting defect that you stumbled across with your misguided rounding work-around. The formatting defect arises even without the explicit rounding.
I quibble with some details of Sergei's explanation of the issue. (See below.) But his SUMPRODUCT solution works because it treats the cell values in a consistent manner, in contrast to COUNTIFS.
Consider the date and time in C3 (2/1/2020 8:49:48 AM, which you format as 8:49).
The exact decimal representation of the binary approximation is 43862.3679166666,6977107524871826171875.
For the COUNTIFS condition that is effectively "<="&C3, Excel replaces C3 with the decimal representation. But Excel formats only up to 15 significant digits.
That should result in "<=43862.3679166667". And if it did, COUNTIFS would correctly count that as 1.
But unfortunately, that particular binary value meets the conditions that evoke the formatting defect, to wit: the integer part is less than 65536, and the remaining binary fraction can be represented in 32 bits or less (31).
So Excel formats the COUNTIFS condition as "<=43862.3679166666", failing to round up the 15th digit as it should, due to the formatting defect.
And since that is less than the value of the date and time in C3, COUNTIFS incorrectly does not count that.
With Sergei's SUMPRODUCT, the comparison expression is effectively C3<=C3. Of course, that is TRUE, no matter how Excel treats C3 in that context. So SUMPRODUCT correctly counts that as 1.
-----
SergeiBaklan wrote: ``Excel works by default with 15 digits and many functions ignore extra digits, but some work in background with 17 digits``
Actually, Excel works with the full binary precision by default. That is the cause of most arithmetic anomalies.
Excel never "works in the background with 17 digits".
But it is true that binary values can be approximated with 17 significant digits, with no loss of precision when converting back to binary. So Excel does represent binary values with up to 17 significant decimal digits when a workbook is saved as an XML file, which includes "xlsx" and "xlsm" files.
Excel never "stores 15 digits of precision", as many articles state incorrectly.
But Excel does format only up to 15 significant digits. And that is what causes the problem with Viz's COUNTIFS formulas, in conjunction with the formatting defect documented in KB 161234.
It is also true that for some comparisons, Excel rounds operands internally to 15 significant digits just for the purpose of the comparison. That is the case with comparison operators ("=", "<", etc) and with COUNTIF[S].
(But interestingly, the internal rounding is not susceptible to the formatting defect.)
(And for some other comparisons, Excel compares the full binary precision. That is the case with lookup functions like MATCH, VLOOKUP, etc, for example.)