Discussion Re: Serious bug with certain decimal numbers: Countifs and Roundup failed in Excel
https://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2315131#M98414
<P><LI-USER uid="146717"></LI-USER> , FYI</P>
<P>Classic <SPAN>Diego Oppenheimer post which explains floating point precision is here <A href="https://www.microsoft.com/en-us/microsoft-365/blog/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers/" target="_blank">Understanding Floating Point Precision, aka “Why does Excel Give Me Seemingly Wrong Answers?” - Microsoft 365 Blog</A></SPAN></P>
<P> </P>
<P><SPAN>Microsoft restructured documentation with introducing of docs.microsoft.com, articles as KB161234 are here. With some updates where applicable. In particular this one is <A href="https://docs.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result" target="_blank">Floating-point arithmetic may give inaccurate result in Excel - Office | Microsoft Docs</A></SPAN></P>Sun, 02 May 2021 14:46:48 GMTSergei Baklan2021-05-02T14:46:48ZSerious bug with certain decimal numbers: Countifs and Roundup failed
https://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2314149#M98346
<P>I have a file that had time upto the seconds level. Some of these numbers had 99999.. after the fifth decimal digit. And all these numbers seem to throw a major bug.<BR /><BR />I wanted to find out how many entries were made on or before the current time. Shocking excel returned the answer as zero for those times or numbers with 99 in the sixth decimal point onwards (which is impossible because the time is already in the array).</P><P> </P><P>I even tried rounding those numbers to five decimal points but it didn't round.<BR /><BR />OpenOffice Calc, Google Sheets and Zoho sheets does not seem to create any such trouble. I have attached the file.</P><P> </P><P>Any thoughts?</P>Sat, 01 May 2021 18:47:07 GMThttps://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2314149#M98346Viz2021-05-01T18:47:07ZRe: Serious bug with certain decimal numbers: Countifs and Roundup failed
https://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2314281#M98352
<P><LI-USER uid="530769"></LI-USER> </P>
<P>Try using MROUND(..., 1/86400)</P>
<P>(1 second = 1/86400 of a day)</P>
<P> </P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="S0357.png" style="width: 400px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/277312iE22DC701B8B57B49/image-size/medium?v=v2&px=400" role="button" title="S0357.png" alt="S0357.png" /></span></P>
<P> </P>Sat, 01 May 2021 21:01:56 GMThttps://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2314281#M98352Hans Vogelaar2021-05-01T21:01:56ZRe: Serious bug with certain decimal numbers: Countifs and Roundup failed
https://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2314687#M98378
<P><LI-USER uid="127945"></LI-USER> wrote: ``Try using MROUND(..., 1/86400)``</P><P> </P><P>I think that is ill-advised.</P><P> </P><P>Consider Sheet1!B4. It display 8:49, but the actual value is 2/1/2020 8:49:48. (I write dates in the form MDY.)</P><P> </P><P>=ISNUMBER(MATCH(<FONT color="#FF0000">MROUND(B4, 1/86400)</FONT>, B4, 0)) returns FALSE(!).</P><P> </P><P>(FYI, we could write "0:0:1" with double-quotes instead 1/86400.)</P><P> </P><P>The reason is: the binary result from MROUND does not match the binary representation of 2/1/2020 8:49:48. In fact, MROUND fails for more than 25% of the values in column B.</P><P> </P><P>An exact binary match is important for lookups, for example.</P><P> </P><P>In contrast....</P><P> </P><P>=ISNUMBER(MATCH(<FONT color="#FF0000">INT(B4)+TEXT(B4,"h:m:s")</FONT>, B4, 0)) returns TRUE for all values in column B.</P><P> </P><P>-----</P><P> </P><P>Caveat: INT(B4)+TEXT(B4,"h:m:s") works for constants that are accurate to the second.</P><P> </P><P>And it works for calculated date and time that results in less than 23:59:59.500.</P><P> </P><P>However, if date and time values might be accurate to the 1/10 second (or less), the following rounds to the second more reliably:</P><P> </P><P>INT(B4)+TEXT(<FONT color="#FF0000">MOD(B4,1)</FONT>,"<FONT color="#FF0000">[h]</FONT>:m:s.000")</P><P> </P><P>The reason is: Excel rounds to the second when formatting to the hour, minute or second.</P><P> </P><P>Consequently, for example, 2/1/2020 23:59:59.6 displays as 2/<STRONG><FONT color="#FF0000">2</FONT></STRONG>/2020 0:00:00.</P><P> </P><P>But INT(B4)+TEXT(B4,"h:m:s") would incorrectly result in 2/<STRONG><FONT color="#FF0000">1</FONT></STRONG>/2020 0:00:00.</P>Sun, 02 May 2021 08:14:52 GMThttps://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2314687#M98378Joe User2021-05-02T08:14:52ZRe: Serious bug with certain decimal numbers: Countifs and Roundup failed
https://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2314730#M98382
<P><LI-USER uid="530769"></LI-USER> </P><P>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.</P><P> </P><P>-----</P><P> </P><P>Yes, Excel has a <EM><U>formatting</U></EM> defect that causes 43863.89142 to be displayed as 43863.8914199999, for example.</P><P> </P><P>(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.)</P><P> </P><P>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.</P><P> </P><P>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.)</P><P> </P><P>Moreover, the effect of the formatting defect can be confusing because when we enter 43863.89142, <EM><U>initially</U></EM> it has the binary value of 43863.89142, even though it appears to be 43863.8914199999. But if we edit the cell, it <U><EM>changes</EM></U> to the binary value of 43863.8914199999, which is different.</P><P> </P><P>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)).</P><P> </P><P>But if we select the cell and press f2, then Enter, the formula returns FALSE.</P><P> </P><P>OTOH, if we have =VALUE("43863.89142") in A1, =COUNTIF(A1, "="&43863.89142) always returns zero because COUNTIF sees the string "=43863.8914199999".</P><P> </P><P>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.</P><P> </P><P>The key is to enter such numbers in double-quotes in an arithmetic expression, when you encounter the formatting defect.</P><P> </P><P>-----</P><P> </P><P>But the real question is: why are you using ROUNDUP(B4,5), in the first place?</P><P> </P><P>In Sheet1, the date and time <EM><U>constants</U></EM> in column B are already accurate to the second. There is no need to round them.</P><P> </P><P>And if there is a need (for example, for <U><EM>calculated</EM></U> date and time values), rounding to 5 decimal places is the wrong way to do it.</P><P> </P><P>Instead, you might use one of the following, depending on your requirements.</P><P> </P><P>TEXT(B4, "m/d/yyyy h:m:s")</P><P>or</P><P>--TEXT(B4, "m/d/yyyy h:m:s")</P><P>or</P><P>INT(B4)+TEXT(B4,"h:m:s")</P><P>or</P><P>INT(B4)+TEXT(MOD(B4,1),"[h]:m:s")</P><P> </P><P>If you provide the <EM><U>original</U></EM> use of COUNTIFS that led to the problem, we can be more specific.</P><P> </P><P>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".</P>Sun, 02 May 2021 09:32:19 GMThttps://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2314730#M98382Joe User2021-05-02T09:32:19ZRe: Serious bug with certain decimal numbers: Countifs and Roundup failed
https://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2314990#M98407
<P><LI-USER uid="146717"></LI-USER> </P><BLOCKQUOTE><HR /><P>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.</P><P> </P><P>The key is to enter such numbers in double-quotes in an arithmetic expression, when you encounter the formatting defect.</P></BLOCKQUOTE><P>Interesting. I've never sever such a workaround before. Will keep it in mind.</P><P> </P><P> </P>Sun, 02 May 2021 12:58:14 GMThttps://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2314990#M98407Detlef Lewin2021-05-02T12:58:14ZRe: Serious bug with certain decimal numbers: Countifs and Roundup failed
https://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2315131#M98414
<P><LI-USER uid="146717"></LI-USER> , FYI</P>
<P>Classic <SPAN>Diego Oppenheimer post which explains floating point precision is here <A href="https://www.microsoft.com/en-us/microsoft-365/blog/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers/" target="_blank">Understanding Floating Point Precision, aka “Why does Excel Give Me Seemingly Wrong Answers?” - Microsoft 365 Blog</A></SPAN></P>
<P> </P>
<P><SPAN>Microsoft restructured documentation with introducing of docs.microsoft.com, articles as KB161234 are here. With some updates where applicable. In particular this one is <A href="https://docs.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result" target="_blank">Floating-point arithmetic may give inaccurate result in Excel - Office | Microsoft Docs</A></SPAN></P>Sun, 02 May 2021 14:46:48 GMThttps://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2315131#M98414Sergei Baklan2021-05-02T14:46:48ZRe: Serious bug with certain decimal numbers: Countifs and Roundup failed
https://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2315170#M98418
<P><LI-USER uid="530769"></LI-USER> </P>
<P>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 <A href="https://www.mrexcel.com/excel-tips/17-or-15-digits-of-precision/" target="_blank">17 or 15 digits of precision - Excel Tips - MrExcel Publishing</A></P>
<P> </P>
<P>For this particular case if use</P>
<LI-CODE lang="excel">=SUMPRODUCT( ($C$1:$C$11<=C4)*($B$1:$B$11=B4))</LI-CODE>
<P>instead of COUNTIFS() it returns correct result.</P>Sun, 02 May 2021 15:05:34 GMThttps://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2315170#M98418Sergei Baklan2021-05-02T15:05:34ZRe: Serious bug with certain decimal numbers: Countifs and Roundup failed
https://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2315360#M98444
<P>Thank you for a detailed response, <LI-USER uid="146717"></LI-USER> and thank you for the alternative solution <LI-USER uid="521"></LI-USER> </P><P> </P><P>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])</P><P> </P><P>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.<BR /><BR />I didn't realise this issue existed in MS Excel. Thank you for helping me understand the bug.</P>Sun, 02 May 2021 17:49:16 GMThttps://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2315360#M98444Viz2021-05-02T17:49:16ZRe: Serious bug with certain decimal numbers: Countifs and Roundup failed
https://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2315381#M98446
<P><LI-USER uid="521"></LI-USER> wrote:</P><P>``Classic Diego Oppenheimer post which explains floating point precision``</P><P>and</P><P>``articles as KB161234 are here. [....] In particular this one is``</P><P> </P><P> </P><P>First, as I stated clearly previously, this <U><EM>formatting defect</EM></U> has <FONT color="#FF0000">nothing to do with 64-bit binary floating-point precision</FONT>.</P><P> </P><P>To demonstrate that fact, note that when we enter 43863.89142, Excel incorrectly displays 43863.8914199999, but <FONT color="#FF0000">VBA correctly displays 43863.89142</FONT>.</P><P> </P><P>And in fact, the binary approximations are different. The following shows the <EM><U>exact</U></EM> decimal representation of the two binary approximations:</P><P> </P><P>43863.89142:</P><P>43863.891419999<FONT color="#FF0000">9,999</FONT>2549419403076171875</P><P> </P><P>43863.8914199999:</P><P>43863.891419999<FONT color="#FF0000">8,980</FONT>62087595462799072265625</P><P> </P><P>(I use period for the decimal point and comma to demarcate the first 15 significant digits.)</P><P> </P><P>-----</P><P> </P><P>Second, the MSFT article that you cite is a retitling of KB 78113, not KB 161234.</P><P> </P><P>But aha! I just stumbled across an archived version of KB 161234 at <A href="https://www.betaarchive.com/wiki/index.php/Microsoft_KB_Archive/161234" target="_blank" rel="noopener">Microsoft KB Archive/161234 - BetaArchive Wiki</A> . For posterity, it reads, in part:</P><P> </P><P>``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``</P><P> </P><P>That KB description is incomplete. The formatting defect is <EM><U>not limited</U></EM> 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 <U><EM>constants</EM></U> and calculations that we <EM><U>explicitly round</U></EM>.</P><P> </P><P>(Obviously, unrounded calculations with decimal fractions are susceptible to the anomalies of binary floating-point arithmetic, which can result in unexpected decimal fraction digits.)</P><P> </P><P>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, <EM><U>initially</U></EM> the value that appears to be 43863.8914199999 has the binary approximation of 43863.89142. It is <EM><U>changed to</U></EM> ("evaluated as") the binary approximation of 43863.8914199999 only <EM><U>after we edit</U></EM> the line in the Formula Bar.</P><P> </P><P>More importantly, the formatting defect is <U><EM>not limited</EM></U> 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.</P><P> </P><P>And <LI-USER uid="530769"></LI-USER>'s examples include 5-digit fractions in that integer range; for example, 43863.89142.</P><P> </P><P>(Caveat: The following is TMI for most people.)</P><P> </P><P>It is difficult to predict the decimal values that evoke the formatting defect.</P><P> </P><P>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:</P><P> </P><P>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</P><P> </P><P>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</P><P> </P><P>3. The 16th significant digit of the exact decimal representation of the binary approximation is 5 or more.</P><P> </P><P>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 <U><EM>should be</EM></U> rounded up, and it is not.</P>Sun, 02 May 2021 23:19:35 GMThttps://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2315381#M98446Joe User2021-05-02T23:19:35ZRe: Serious bug with certain decimal numbers: Countifs and Roundup failed
https://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2315596#M98454
<P><LI-USER uid="127945"></LI-USER> </P><P> </P><P>Thank you, Hans.</P><P> </P><P>It almost sorted the problem. But there were still a few numbers that it couldn't fix. As Joe mentioned, there seems to be some inherent problem in certain odd cases.<BR /><BR />The solution that Sergei gave to use sumproduct in place of Countif fixed the problem.</P>Sun, 02 May 2021 20:02:36 GMThttps://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2315596#M98454Viz2021-05-02T20:02:36ZRe: Serious bug with certain decimal numbers: Countifs and Roundup failed
https://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2316325#M98474
<P><LI-USER uid="530769"></LI-USER> </P><P>After a lot of rummaging around on Google, I came to this result.</P><P>The short answer is, you can't edit your numbers the way you want after you've entered the numeric value and hit enter. The moment you do this, the number is truncated to 15 digits and shown as exponential.</P><P> </P><P>This is not a bug, it is a function.</P><P> </P><P>Everything you do after that uses the truncated value, so no formatting trick is helpful. However, you can use the “Text to columns“ option to convert the exponential notation to text (click on the column heading, click “Text to data“ then click Delimited, Next uncheck all delimiting boxes, select “Text in Column data format "And then from" Finish ") to convert the values into text and immediately display them as a 15-digit number. But it will only be 15 digits so if you had a longer number the pause is lost.</P><P> </P><P>To have the number in the worksheet exactly as you entered it, you need to save it as text. So you have to prepend an apostrophe or format cells as text before you can enter / copy values. If it "sometimes doesn't work" then you are either doing something wrong or you have some kind of autocorrect enabled (if you use an apostrophe and a large number> 15 digits, Excel will treat it as an incorrect value and mark the cell with a warning hence it is this remark is neither personal nor critical.</P><P> </P><P>Another way to do this in bulk is to import values from a text file. Dare I say it's the only way out for most situations. Be sure to import the file and not just open it, as Excel treats the csv type like the native format and of course cuts large numbers to 15 digits.</P><P> </P><P>The bottom line is, if you type a large numeric value into an Excel cell formatted as text, it will continue to be displayed in scientific notation as long as the cell is not wide enough. It's annoying, but a side effect of some of Excel's internal algorithms. If you just make the cell wider you will see the full value every time.</P><P> </P><P>A pleasant task for most simple users like me, when you have nothing to do or don't want to do anything.</P><P> </P><P>With my little knowledge, I cannot 100% verify whether all of this is the case.</P><P> </P><P>Who am I already! ... I know that I don't know anything.</P><P>Nikolino</P><P> </P><P> </P>Mon, 03 May 2021 08:02:25 GMThttps://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2316325#M98474NikolinoDE2021-05-03T08:02:25ZRe: Serious bug with certain decimal numbers: Countifs and Roundup failed
https://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2316599#M98486
<P><LI-USER uid="722750"></LI-USER> wrote: ``The moment you do this, the number is truncated to 15 digits and shown as exponential.``</P><P> </P><P>Hey, Niko, I think you responded to the wrong thread. If you agree, you can delete your response, and I'll delete mine.</P><P> </P><P>Nothing about Viz's problem has anything to do with trying to enter more than 15 significant digits.</P><P> </P><P>He is entering date and time perhaps in the form 2/1/2020 8:49:48 AM. At least, that is how column C appears to me, due to my regional settings. (My date is in the form MDY.)</P><P> </P>Mon, 03 May 2021 09:14:43 GMThttps://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2316599#M98486Joe User2021-05-03T09:14:43ZRe: Serious bug with certain decimal numbers: Countifs and Roundup failed
https://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2317023#M98502
<P><LI-USER uid="146717"></LI-USER> </P><P><SPAN class="VIiyi"><SPAN class="JLqJ4b ChMk0b"><SPAN>If the author thinks that the different behavior of the cells is not based on this (different formatting) and my comment for you means that it is bad / wrong / annoying, I of course take back my comment and apologize for the inconvenience.</SPAN></SPAN> </SPAN></P><P><SPAN class="VIiyi"><SPAN class="JLqJ4b ChMk0b"><SPAN>You do not need to delete yours as compensation.</SPAN></SPAN> </SPAN></P><P> </P><P><SPAN class="VIiyi"><SPAN class="JLqJ4b ChMk0b"><SPAN>Comments are there to help further, to suggest possible solutions, </SPAN></SPAN></SPAN></P><P><SPAN class="VIiyi"><SPAN class="JLqJ4b ChMk0b"><SPAN>to reveal other solutions and so much more.</SPAN></SPAN> </SPAN></P><P><SPAN class="VIiyi"><SPAN class="JLqJ4b ChMk0b"><SPAN>Comments are not there to disturb or irritate or to fulfill any end in itself.</SPAN></SPAN></SPAN></P><P> </P><P>Thank you for your understanding and patience</P><P> </P><P> </P><P>Nikolino</P><P>I know I don't know anything (Socrates)</P>Mon, 03 May 2021 11:44:38 GMThttps://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2317023#M98502NikolinoDE2021-05-03T11:44:38ZRe: Serious bug with certain decimal numbers: Countifs and Roundup failed
https://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2320579#M98619
<P><LI-USER uid="530769"></LI-USER> wrote: ``thank you for the alternative solution <LI-USER uid="521"></LI-USER>``</P><P><BR />I agree with <LI-USER uid="521"></LI-USER>'s SUMPRODUCT alternative to solve your problem with COUNTIFS. I suggest that you mark his response as "best" or "answer".</P><P> </P><P>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.</P><P> </P><P>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.</P><P> </P><P>Consider the date and time in C3 (2/1/2020 8:49:48 AM, which you format as 8:49).</P><P> </P><P>The exact decimal representation of the binary approximation is 43862.3679166666,<FONT color="#FF0000">697</FONT>7107524871826171875.</P><P> </P><P>For the COUNTIFS condition that is effectively "<="&C3, Excel replaces C3 with the decimal representation. But Excel formats only up to 15 significant digits.</P><P> </P><P>That should result in "<=43862.3679166667". And if it did, COUNTIFS would correctly count that as 1.</P><P> </P><P>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).</P><P> </P><P>So Excel formats the COUNTIFS condition as "<=43862.3679166666", failing to round up the 15th digit as it should, due to the formatting defect.</P><P> </P><P>And since that is less than the value of the date and time in C3, COUNTIFS incorrectly does not count that.</P><P> </P><P>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.</P><P> </P><P>-----</P><P> </P><P><LI-USER uid="521"></LI-USER> wrote: ``Excel works by default with 15 digits and many functions ignore extra digits, but some work in background with 17 digits``</P><P> </P><P>Actually, Excel works with the <U><EM>full binary precision</EM></U> by default. That is the cause of most arithmetic anomalies.</P><P> </P><P>Excel never "works in the background with 17 digits".</P><P> </P><P>But it is true that binary values can be <EM><U>approximated</U></EM> 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.</P><P> </P><P>Excel never "stores 15 digits of precision", as many articles state incorrectly.</P><P> </P><P>But Excel does <EM><U>format</U></EM> 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.</P><P> </P><P>It is also true that for <EM><U>some</U></EM> 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].</P><P> </P><P>(But interestingly, the internal rounding is not susceptible to the formatting defect.)</P><P> </P><P>(And for <U><EM>some other</EM></U> comparisons, Excel compares the full binary precision. That is the case with lookup functions like MATCH, VLOOKUP, etc, for example.)</P><P> </P><P> </P>Tue, 04 May 2021 18:54:53 GMThttps://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2320579#M98619Joe User2021-05-04T18:54:53ZRe: Serious bug with certain decimal numbers: Countifs and Roundup failed
https://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2321054#M98629
<P><LI-USER uid="530769"></LI-USER> </P><P>That's very interesting,</P><P>So the essence of this rounding issue seems to be for numbers like x=43863.89142,</P><P>=COUNTIF(x,x)<BR />=> 1</P><P>as expected, but</P><P>=COUNTIF(x,"="&x)<BR />=> 0 ?!</P><P>A safer approach is to convert date/times to integers for comparisons by multiplying by 24*60*60.</P><P> </P>Tue, 04 May 2021 10:07:11 GMThttps://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2321054#M98629lori_m2021-05-04T10:07:11ZRe: Serious bug with certain decimal numbers: Countifs and Roundup failed
https://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2321198#M98639
<P><LI-USER uid="288074"></LI-USER> </P>
<P>As I remember such behaviour is for all <DO>IF() functions, e.g. =SUMIF(x,"="&x, x) =>0 vs</P>
<P>=SUMIF(x,x,x) =>x </P>Tue, 04 May 2021 10:47:20 GMThttps://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2321198#M98639Sergei Baklan2021-05-04T10:47:20ZRe: Serious bug with certain decimal numbers: Countifs and Roundup failed
https://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2321325#M98643
<P><LI-USER uid="521"></LI-USER> </P><P>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.</P>Tue, 04 May 2021 11:27:47 GMThttps://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2321325#M98643lori_m2021-05-04T11:27:47ZRe: Serious bug with certain decimal numbers: Countifs and Roundup failed
https://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2342359#M99421
<P><LI-USER uid="288074"></LI-USER> </P><P> </P><P>Thank you for the solution. Especially since Sumproduct doesn't spill, for DA this seems to be the best work around.<BR /><BR />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.89141</P>Mon, 10 May 2021 19:34:17 GMThttps://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2342359#M99421Viz2021-05-10T19:34:17ZRe: Serious bug with certain decimal numbers: Countifs and Roundup failed
https://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2343918#M99475
<P><LI-USER uid="530769"></LI-USER> </P><P>The 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 <A href="https://en.wikipedia.org/wiki/Microsoft_Binary_Format" target="_blank" rel="noopener">https://en.wikipedia.org/wiki/Microsoft_Binary_Format </A>)</P><P> </P><P>Since technical documentation is lacking, I had collected together some findings on numeric formats <A href="https://github.com/lhem/excel/wiki/Numbers" target="_self">here</A>, one being 'f<SPAN>or accurate timing calculations serial numbers should be scaled by the number of milliseconds per day'</SPAN>.</P>Tue, 11 May 2021 09:26:02 GMThttps://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2343918#M99475lori_m2021-05-11T09:26:02ZRe: Serious bug with certain decimal numbers: Countifs and Roundup failed
https://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2347508#M99558
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.Wed, 12 May 2021 05:28:19 GMThttps://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2347508#M99558Viz2021-05-12T05:28:19ZRe: Serious bug with certain decimal numbers: Countifs and Roundup failed
https://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2347670#M99563
<P><LI-USER uid="530769"></LI-USER> </P><P>Yes and I think that the 32-bit representation for the decimal fraction that <LI-USER uid="146717"></LI-USER> 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.</P>Wed, 12 May 2021 06:44:13 GMThttps://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2347670#M99563lori_m2021-05-12T06:44:13ZRe: Serious bug with certain decimal numbers: Countifs and Roundup failed
https://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2347946#M99571
<P><LI-USER uid="530769"></LI-USER> wrote: ``interestingly this problem arises only if the integer part is above 1024. If I try 1023.89142, it seems to work very well``</P><P> </P><P>It is true that the formatting defect is evident with the decimal fraction 0.89142 only for numbers with integers 1024 to 65535.</P><P> </P><P>But the formatting defect is <EM><U>not limited</U></EM> to integer parts in that range.</P><P> </P><P>As I wrote previously, the formatting defect can arise with <U><EM>any integer part</EM></U> from 1 to 65535, and even when the integer part is zero if the fractional part is more than 0.5.</P><P> </P><P>For example: </P><P>0.570504326839</P><P>1.46172848274</P><P>2.9240934595</P><P>4.514913772</P><P>8.33628761</P><P>16.53976272</P><P>32.1072193</P><P>64.1050636</P><P>128.1007522</P><P>256.106409</P><P>512.100477</P><P> </P><P>But your original problem involved the formatting defect with date+times accurate to the second.</P><P> </P><P>I have determined that the formatting defect arises with the following:</P><P>1. 128 of 86400 times (0.15%) for 3/19/1911 to 6/4/1922</P><P>2. 256 of 86400 times (0.30%) for 6/5/1922 to 11/7/1944</P><P>3. 640 of 86400 times (0.74%) for 11/8/1944 to 9/16/1989</P><P>4. 1280 of 86400 times (1.48%) for 9/17/1989 to 6/4/2079</P><P> </P><P>The formatting defect does not arise for 3/18/1911 and earlier -- again, for date+times accurate to the second.</P><P> </P><P>(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.)</P><P> </P>Wed, 12 May 2021 09:05:57 GMThttps://techcommunity.microsoft.com/t5/excel/serious-bug-with-certain-decimal-numbers-countifs-and-roundup/m-p/2347946#M99571Joe User2021-05-12T09:05:57Z