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.
I think there are two separate issues, and it is unclear if the issue that you focused on is truly important for your purposes -- although it is interesting curiosity.
-----
Yes, Excel has a formatting defect that causes 43863.89142 to be displayed as 43863.8914199999, for example.
(MSFT used to document the defect in KB161234. But I cannot find that document or the equivalent online anymore. No matter. The KB description was inaccurate, anyway.)
The defect is specific to Excel. It has nothing to do with the 64-bit binary floating-point standard. That is why you do not see it in other applications.
And the formatting defect is not limited to ROUNDUP. The formatting defect arises with relatively few binary values that meet very specific conditions. (I can explain, if you wish. It is TMI for most people.)
Moreover, the effect of the formatting defect can be confusing because when we enter 43863.89142, initially it has the binary value of 43863.89142, even though it appears to be 43863.8914199999. But if we edit the cell, it changes to the binary value of 43863.8914199999, which is different.
For example, initially =ISNUMBER(MATCH(VALUE("43863.89142"), { 43863.89142 }, 0)) returns TRUE, even though it appears to be =ISNUMBER(MATCH(VALUE("43863.89142"), { 43863.8914199999 }, 0)).
But if we select the cell and press f2, then Enter, the formula returns FALSE.
OTOH, if we have =VALUE("43863.89142") in A1, =COUNTIF(A1, "="&43863.89142) always returns zero because COUNTIF sees the string "=43863.8914199999".
The only work-around that I know is to replace such constants with expressions like VALUE("43863.89142"), ROUND("43863.89142",5), A1+"43863.89142", etc.
The key is to enter such numbers in double-quotes in an arithmetic expression, when you encounter the formatting defect.
-----
But the real question is: why are you using ROUNDUP(B4,5), in the first place?
In Sheet1, the date and time constants in column B are already accurate to the second. There is no need to round them.
And if there is a need (for example, for calculated date and time values), rounding to 5 decimal places is the wrong way to do it.
Instead, you might use one of the following, depending on your requirements.
TEXT(B4, "m/d/yyyy h:m:s")
or
--TEXT(B4, "m/d/yyyy h:m:s")
or
INT(B4)+TEXT(B4,"h:m:s")
or
INT(B4)+TEXT(MOD(B4,1),"[h]:m:s")
If you provide the original use of COUNTIFS that led to the problem, we can be more specific.
I think what you posted in your attachment is your attempts to understand and work around the original problem. I "cannot see the forest for the trees".
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.)