COUNTIF Formula

COUNTIF Formula - Bug?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AndyR - 01 Nov 2007 14:48 GMT

Hi all,

Having a strange problem with a COUNTIF formula.

The formula is:
=COUNTIF('Day 1 - EEA'!D9:D32,">0:25")+COUNTIF('Day 2 - EEA'!
D9:D32,">0:25")+COUNTIF('Day 3 - EEA'!D9:D32,">0:25")+COUNTIF('Day 4 -
EEA'!D9:D32,">0:25")+COUNTIF('Day 5 - EEA'!D9:D32,">0:25")
+COUNTIF('Day 6 - EEA'!D9:D32,">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.

Reply to this Message

Cavy - 01 Nov 2007 15:13 GMT

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

> Hi all,
>
[quoted text clipped - 25 lines]
>
> Any help would be great.

Reply to this Message

AndyR - 01 Nov 2007 15:25 GMT

I just tested with =COUNTIF('Day 1 - EEA'!D9:D32,">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.

Reply to this Message

Peo Sjoblom - 01 Nov 2007 15:40 GMT

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

Signature

Regards,

Peo Sjoblom

>I just tested with =COUNTIF('Day 1 - EEA'!D9:D32,">0.017361111")
>
[quoted text clipped - 10 lines]
> I hope I just didn't alter it right as I don't understand what's going
> wrong.

Reply to this Message

AndyR - 01 Nov 2007 16:10 GMT

You're a legend. Thank you very much!

Reply to this Message

Rick Rothstein (MVP - VB) - 01 Nov 2007 16:06 GMT

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

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

Rick

>I just tested with =COUNTIF('Day 1 - EEA'!D9:D32,">0.017361111")
>
[quoted text clipped - 10 lines]
> I hope I just didn't alter it right as I don't understand what's going
> wrong.

Reply to this Message