count of time
Posted by Ken Aldag on January 23, 2001 6:03 AM
I have a column with time values such as :32, :54 and so forth for a whole month. I would like to do a count of 0-15 seconds and 16-30, 31-45, 46-60 and greater that 1 minute. I can acomplish the 0-15 and greater than 1:00 but the others have me stumped. HELP
Ken
Posted by Dan on January 28, 2001 5:29 AM
Ken,
Here's a crude but effective idea :
Count 0-15 =COUNTIF($A$2:$A$10,"<00:00:15")
Count 16-30 =COUNTIF($A$2:$A$10,"<00:00:30")-COUNTIF($A$2:$A$10,"<=00:00:15")
Count 31-45 =COUNTIF($A$2:$A$10,"<00:00:45")-COUNTIF($A$2:$A$10,"<=00:00:30")
Count 46-60 =COUNTIF($A$2:$A$10,"<=00:00:60")-COUNTIF($A$2:$A$10,"<=00:00:45")
Count 1 minute+ =COUNTIF($A$2:$A$10,">00:01:00")
Obviously you'll need to change the range to match your sheet.
Regards,
Dan.
Posted by Dan on January 28, 2001 5:30 AM
Ken,
Here's a crude but effective idea :
Count 0-15 =COUNTIF($A$2:$A$10,"<=00:00:15")
Count 16-30 =COUNTIF($A$2:$A$10,"<00:00:30")-COUNTIF($A$2:$A$10,"<=00:00:15")
Count 31-45 =COUNTIF($A$2:$A$10,"<00:00:45")-COUNTIF($A$2:$A$10,"<=00:00:30")
Count 46-60 =COUNTIF($A$2:$A$10,"<=00:00:60")-COUNTIF($A$2:$A$10,"<=00:00:45")
Count 1 minute+ =COUNTIF($A$2:$A$10,">00:01:00")
Obviously you'll need to change the range to match your sheet.
Regards,
Dan.
Posted by Aladin Akyurek on January 29, 2001 6:34 AM
Dan: There is nothing crude about your formulas, if your assumption about is correct. The cells where your formula go must formatted as general or number. You can avoid that by using VALUE-function. E.g.,
=COUNTIF($A$2:$A$10,"<"&VALUE("00:00:15"))
BTW. You can also array formulas that are equivalent to yours in effectiveness (which must be entered by hitting control+shift+enter instead of just hitting enter key alone). E.g.,
=SUM(1*(A2:A10 LessThanEqualTo VALUE("00:00:15")))
=SUM(1*(A2:A10>=VALUE("00:00:16"))*(A2:A10 LessThanEqualTo VALUE("00:00:30")))
=SUM(1*(A2:A10>VALUE("00:01:00")))
etc.
Note. Replace "LessThanEqualTo" by the appropriate strings of Excel relational symbols.
Aladin
