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.

lori_m's avatar
lori_m
Iron Contributor
May 04, 2021

Viz 

That's very interesting,

So the essence of this rounding issue seems to be for numbers like x=43863.89142,

=COUNTIF(x,x)
=> 1

as expected, but

=COUNTIF(x,"="&x)
=> 0 ?!

A safer approach is to convert date/times to integers for comparisons by multiplying by 24*60*60.

 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 04, 2021

lori_m 

As I remember such behaviour is for all <DO>IF() functions, e.g. =SUMIF(x,"="&x, x) =>0 vs

=SUMIF(x,x,x) =>x 

  • lori_m's avatar
    lori_m
    Iron Contributor
    May 04, 2021

    SergeiBaklan 

    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.

Resources