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.
JoeUser2004 , FYI
Classic Diego Oppenheimer post which explains floating point precision is here Understanding Floating Point Precision, aka “Why does Excel Give Me Seemingly Wrong Answers?” - Microsoft 365 Blog
Microsoft restructured documentation with introducing of docs.microsoft.com, articles as KB161234 are here. With some updates where applicable. In particular this one is Floating-point arithmetic may give inaccurate result in Excel - Office | Microsoft Docs
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.
- It took me a while to process what you have written. Interestingly that the problem does not happen if integer is more than 65535. And I noticed that it didn't happen for values below 1024 either.
Viz wrote: ``interestingly this problem arises only if the integer part is above 1024. If I try 1023.89142, it seems to work very well``
It is true that the formatting defect is evident with the decimal fraction 0.89142 only for numbers with integers 1024 to 65535.
But the formatting defect is not limited to integer parts in that range.
As I wrote previously, the formatting defect can arise with any integer part from 1 to 65535, and even when the integer part is zero if the fractional part is more than 0.5.
For example:
0.570504326839
1.46172848274
2.9240934595
4.514913772
8.33628761
16.53976272
32.1072193
64.1050636
128.1007522
256.106409
512.100477
But your original problem involved the formatting defect with date+times accurate to the second.
I have determined that the formatting defect arises with the following:
1. 128 of 86400 times (0.15%) for 3/19/1911 to 6/4/1922
2. 256 of 86400 times (0.30%) for 6/5/1922 to 11/7/1944
3. 640 of 86400 times (0.74%) for 11/8/1944 to 9/16/1989
4. 1280 of 86400 times (1.48%) for 9/17/1989 to 6/4/2079
The formatting defect does not arise for 3/18/1911 and earlier -- again, for date+times accurate to the second.
(I suspect the formatting defect arises more often with date+times accurate to the millisecond. But I have not played with such times. I feel like we are beating dead horse.)
Yes and I think that the 32-bit representation for the decimal fraction that JoeUser2004 mentions looks like it ties in with the 40-bit MS binary format from the link which uses 8 bits for the exponent and the remaining 32 bits for sign and mantissa.
