Counting time-frames

[Ken G.]

Not sure about the subject - best description I could come up with.
I have a column which shows elapsed times in hours and minutes for various
jobs. What I want to do is count how many of these fall into various time
frames e.g 1 hour to 1 hour and 30 minutes, 1hour and 31 minutes to 2 hours
etc. All I need is a count. I don't want to have to the sort the sheet to do
this as it needs to be part of the overall sheet, and if possible I'd like to
avoid vba although if that is the only way around it - so be it.

 

[Nayab]

Not sure about the subject - best description I could come up with.
I have a column which shows elapsed times in hours and minutes for various
jobs. What I want to do is count how many of these fall into various time
frames e.g 1 hour to 1 hour and 30 minutes, 1hour and 31 minutes to 2 hours
etc. All I need is a count. I don't want to have to the sort the sheet todo
this as it needs to be part of the overall sheet, and if possible I'd like to
avoid vba although if that is the only way around it - so be it.

Suppose you need to find the numbers lying in the bucket 0.75 -1,
1-1.25, 1.25-1.5, 1.5-1.75, 1.75-2
then, if your input is in column A as
0.75
0.76
0.96
1.9
1.2
1.4
then do a vlookup in column B of the sort =VLOOKUP($A1,$M$1:$N$6,2,1)

where M1 to N6 contains
0.75 a
1 b
1.25 c
1.5 d
1.75 e
2 f


and then in column C you can find the count by using =COUNTIF($B$1:$B
$6,N1)

Final sheet looks like:

0.75 a 3 0.75-1 0.75 a
0.76 a 1 1-1.25 1 b
0.96 a 1 1.25-1.5 1.25 c
1.9 e 0 1.5-1.75 1.5 d
1.2 b 1 1.75-2 1.75 e
1.4 c 0 2 f


Its difficult to explain this way and will make sense if I can share
my spreadsheet with you. But, I hope this helps to some extent.

 

[Ken G.]

Thanks. This did the job.


: