Group a set of 'time'
 

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

How can i do this in Excel?

Group a set of 'time'
 

Try:

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

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

--
HTH

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


Replace @mailinator.com with @tiscali.co.uk


"lelynx" wrote in message
...
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

How can i do this in Excel?

Group a set of 'time'
 

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


"lelynx" wrote:

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

How can i do this in Excel?

Group a set of 'time'
 

Alternative:

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

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


"lelynx" wrote:

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

How can i do this in Excel?