Serious bug with certain decimal numbers: Countifs and Roundup failed

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.)

SergeiBaklan  wrote:

``Classic Diego Oppenheimer post which explains floating point precision``

and

``articles as KB161234 are here. [....] In particular this one is``

First, as I stated clearly previously, this formatting defect has nothing to do with 64-bit binary floating-point precision.

To demonstrate that fact, note that when we enter 43863.89142, Excel incorrectly displays 43863.8914199999, but VBA correctly displays 43863.89142.

And in fact, the binary approximations are different.  The following shows the exact decimal representation of the two binary approximations:

43863.89142:

43863.8914199999,9992549419403076171875

43863.8914199999:

43863.8914199998,98062087595462799072265625

(I use period for the decimal point and comma to demarcate the first 15 significant digits.)

-----

Second, the MSFT article that you cite is a retitling of KB 78113, not KB 161234.

But aha! I just stumbled across an archived version of KB 161234 at Microsoft KB Archive/161234 - BetaArchive Wiki .  For posterity, it reads, in part:

``when you type or calculate a number between 32,768 and 65,535 that contains a decimal portion of .848, the number is evaluated and displayed in the formula bar with a decimal portion of .8479999999. [....] Microsoft has confirmed this to be a problem``

That KB description is incomplete.  The formatting defect is not limited to the list Excel versions (5.0, 95 and 97).  Obviously, it applies to the latest Excel versions as well.  And the emphasis should be on constants and calculations that we explicitly round.

(Obviously, unrounded calculations with decimal fractions are susceptible to the anomalies of binary floating-point arithmetic, which can result in unexpected decimal fraction digits.)

And I take issue with the statement:  ``.848 [...] is evaluated [...] with a decimal portion of .8479999999``.  As I noted previously, when we enter 43863.89142, initially the value that appears to be 43863.8914199999 has the binary approximation of 43863.89142.  It is changed to ("evaluated as") the binary approximation of 43863.8914199999 only after we edit the line in the Formula Bar.

More importantly, the formatting defect is not limited to that integer range and that decimal fraction.  For example, for that integer range, other 3-digit fractions are 0.098, 0.223, 0.348, 0.473, 0.598, 0.723 and 0.973 as well as 0.848.

And Viz's examples include 5-digit fractions in that integer range; for example, 43863.89142.

(Caveat: The following is TMI for most people.)

It is difficult to predict the decimal values that evoke the formatting defect.

And it is difficult for me to explain the conditions that evoke the formatting defect without resorting to the details of the binary representation.  Those conditions are:

1. The integer part of the number is 65535 or less.  If the integer part is zero, the decimal fraction must exceed 0.5.  And

2. The binary approximation of the decimal fraction can be represented in 32 bits; that is, all the bits to the right are zero.  And

3. The 16th significant digit of the exact decimal representation of the binary approximation is 5 or more.

I'm sure the 3rd condition is not explicit in the Excel implementation.  But we notice a formatting defect only when the 15th significant digit should be rounded up, and it is not.