Group a set of 'time'

  • Thread starter Thread starter lelynx
  • Start date Start date
L

lelynx

Hi,

I have a set of data in time format as such...

0:10:38
0:10:36
0:08:57
0:31:44
0:30:39
0:29:19
0:28:23
0:28:15
0:25:09

What i want to do is to do a count to get something like this...
range count
0 - 5 min 0
6 - 10 min 10
11 min 15
Click to expand...

How can i do this in Excel?
 
Try:

=SUMPRODUCT(--($A$1:$A$9<=TIME(0,5,0)))
=SUMPRODUCT(--($A$1:$A$9>TIME(0,5,0)),--($A$1:$A$9<=TIME(0,10,0)))
=SUMPRODUCT(--($A$1:$A$9>TIME(0,10,0)))

or
=SUMPRODUCT(--($A$1:$A$9<=5/1440))
==SUMPRODUCT((A1:A9>5/1440)*(A1:A9<=10/1440))
=SUMPRODUCT(--($A$1:$A$9>10/1440))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
This might do - my results:

0 - 5 min 0 0 00:05 0
6 - 10 min 10 10 00:10 1
11 - 15mins 15 00:15 2
15mins + 15+ 6

Forumula

{=SUM(IF(K24:K32<H27,1,0))}
{=(SUM(IF(K24:K32<H29,1,0))-I28)}

Where K24,K32 is your list of times
Where H27,H29 relate to the max time i.e. 00:05 and in H29 00:15
must be an array
the second equation deducts any previous values
 
Alternative:

=COUNTIF(A:A,"<"&B2)-COUNTIF(A:A,"<="&B1)

where B2 and B1 relate to specific times i.e 00:05 and 00:15
 
Back
Top