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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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.
 
Upvote 0
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...
 
Upvote 0
What does this do? Excel doesnt really like negative time.

=COUNTIFS(P:P,"<0",P:P,">"&-TIME(0,2,0))
 
Upvote 0
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.

 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top