Forum Discussion

Viz's avatar
Viz
Brass Contributor
May 01, 2021
Solved

Serious bug with certain decimal numbers: Countifs and Roundup failed

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. I wanted to find out how many ...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    May 02, 2021

    Viz 

    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.

HansVogelaar's avatar
HansVogelaar
MVP
May 01, 2021

Viz 

Try using MROUND(..., 1/86400)

(1 second = 1/86400 of a day)

 

 

JoeUser2004's avatar
JoeUser2004
Bronze Contributor
May 02, 2021

HansVogelaar  wrote: ``Try using MROUND(..., 1/86400)``

 

I think that is ill-advised.

 

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

 

=ISNUMBER(MATCH(MROUND(B4, 1/86400), B4, 0)) returns FALSE(!).

 

(FYI, we could write "0:0:1" with double-quotes instead 1/86400.)

 

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.

 

An exact binary match is important for lookups, for example.

 

In contrast....

 

=ISNUMBER(MATCH(INT(B4)+TEXT(B4,"h:m:s"), B4, 0)) returns TRUE for all values in column B.

 

-----

 

Caveat: INT(B4)+TEXT(B4,"h:m:s") works for constants that are accurate to the second.

 

And it works for calculated date and time that results in less than 23:59:59.500.

 

However, if date and time values might be accurate to the 1/10 second (or less), the following rounds to the second more reliably:

 

INT(B4)+TEXT(MOD(B4,1),"[h]:m:s.000")

 

The reason is:  Excel rounds to the second when formatting to the hour, minute or second.

 

Consequently, for example, 2/1/2020 23:59:59.6 displays as 2/2/2020 0:00:00.

 

But INT(B4)+TEXT(B4,"h:m:s") would incorrectly result in 2/1/2020 0:00:00.

Resources