COUNTIF Formula - Bug?

[AndyR]

Hi all,

Having a strange problem with a COUNTIF formula.

The formula is:
=COUNTIF('Day 1 - EEA'!D932,">0:25")+COUNTIF('Day 2 - EEA'!
D932,">0:25")+COUNTIF('Day 3 - EEA'!D932,">0:25")+COUNTIF('Day 4 -
EEA'!D932,">0:25")+COUNTIF('Day 5 - EEA'!D932,">0:25")
+COUNTIF('Day 6 - EEA'!D932,">0:25")

Working with cells in a 'h:mm' format. The formula in these cells are
a simple =C9-B9 calculation to tell me the total time difference.
Format is C9 & B9 is 'hh:mm'

So if B9 is 10:00 and C9 is 10:30, the answer is 0:30

The formula at the top is to tell me how many times the difference is
over 25 minutes.

The problem:

Sometimes it doesn't work...for example if I put 16:01 in B9 and 16:26
in C9, the answer shows as 0:25 but the overall formula counts it as
an entry OVER 0:25.

Really not sure what to do as all the formatting seems correct and it
doesn't always count 0:25...

Any help would be great.

 

[Cavy]

Hi Andy,

Have you tried replacing ">0:25" in the formula by 0.017361111, which is the
number that represents 0:25 minutes?

That may give you the answer you are looking for

 

[AndyR]

I just tested with =COUNTIF('Day 1 - EEA'!D932,">0.017361111")

If that's what you meant then it doesn't appear to work either.

I just tested one cell with my original formula. If the cells (B9 and
C9) show 16:01 & 16:26, the answer is 0:25 but the formula counts it.

But if the cells (B9 and C9) show 13:01 and 13:26, the answer is
still 0:25 but the formula doesn't count it...

If I use your formula it counts both.

I hope I just didn't alter it right as I don't understand what's going
wrong.

 

[Peo Sjoblom]

The reason is here

http://www.cpearson.com/excel/rounding.htm


it's not a bug, it's the way computers do calculations

You could round down the values to the nearest minute if you never use
seconds in your calculations

=FLOOR(C9-B9,TIME(,1,))


if you use seconds then round to the nearest minute


=ROUND((C9-B9)/TIME(,1,),0)*TIME(,1,)


Then the count will work


--


Regards,


Peo Sjoblom

 

[Rick Rothstein \(MVP - VB\)]

What happens if you let Excel handle the fractions for you? Give this a
try...

=COUNTIF('Day 1 - EEA'!D932,">"&TIME(0,25,0))

Rick

 

[AndyR]

You're a legend. Thank you very much!