counting ranges of negative time values

[nn992]

Hello everyone,

I have giant file containing various time values. I have to sort those times according to frequency. What I managed to do is to sort them like:

00:00:00 123 occurences

between 0 and 1 456 occurences

between 1 and 2 789 occurences

etc...

What I cant do is same thing, but for NEGATIVE numbers. I was using countifs function for positive values, but for negatives it always responds with 0 as a result of occurences.


Any advice/ help?

Thanks

 

[steve the fish]

The COUNTIFS is as simple as:

=COUNTIFS(A:A,"<0")

If it produces 0 as the answer then your times are not times, they will be textual.

 

[nn992]

when I enter countifs(P:P;"<00:00:00";P:P;">-00:02:00")

he should respond with frequency numbers between 0 and -2 minutes, right? I get response 0, even though there is bunch on numbers which fulfill this criterion...

 

[steve the fish]

What does this do? Excel doesnt really like negative time.

=COUNTIFS(P:P,"<0",P:P,">"&-TIME(0,2,0))

 

[nn992]

still returns zero as ansver in all cases...

 

[NARAYANK991]

Hi ,

As already mentioned , Excel displays negative time as a series of # symbols.

However , if you change your column format to General , you can easily see all the negative numbers.

A time value such as 00:02:00 is numerically 0.00139 ; thus when you have a negative number which is the equivalent of 00:02:00 , it should be -0.00139 ; when you change your column format to General , are there values between -0.00139 and 0 ?

If so , Steve's formula :

=COUNTIFS(P:P,"<0",P:P,">"&-TIME(0,2,0))

should work.