counting ranges of negative time values

nn992

New Member
Joined
Jul 28, 2016
Messages
47
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
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,080
Office Version
365
Platform
Windows
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

New Member
Joined
Jul 28, 2016
Messages
47
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

Well-known Member
Joined
Oct 20, 2009
Messages
8,080
Office Version
365
Platform
Windows
What does this do? Excel doesnt really like negative time.

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

NARAYANK991

Board Regular
Joined
Jan 10, 2012
Messages
216
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.

 

Watch MrExcel Video

Forum statistics

Threads
1,102,360
Messages
5,486,387
Members
407,544
Latest member
mguevara

This Week's Hot Topics

Top