call distribution

[Andrew@rushington]

i have a list of telephone calls recieved with the time of the call over a 1
day (8hour) period. i need to report how many calls recieved in 15 min
intervals. useing excel 2003

 

[Mike H]

Hi,
With your times in columnA range A1 - A150
Build your self a table that looks like this and covers your time period:-

Col C Col D
08:00 08:15
08:15 08:30
08:30 08:45
08:45 09:00
09:00 09:15
09:15 09:30
09:30 09:45
09:45 10:00
10:00 10:15
10:15 10:30
10:30 10:45
10:45 11:00
11:00 11:15
11:15 11:30
11:30 11:45
11:45 12:00
12:00 12:15
12:15 12:30
12:30 12:45
12:45 13:00
13:00 13:15
13:15 13:30
13:30 13:45
13:45 14:00
14:00 14:15

In my case this is in columns C & D
In E1 type
=(COUNTIF($A$1:$A$150,">="&C1))-(COUNTIF($A$1:$A$150,">="&D1))
Drag down to the same length as the times in columns C&D

Mike

 

[Pete_UK]

Call data usually covers the calls you make, rather than calls
received.

It would be better to draw up a list of 15minute intervals on a
separate sheet - perhaps starting in A1. You can do this by entering
00:00:00 in A1, then in A2 entering this formula:

=A1+TIME(0,15,0)

and copying this down the column to cover a full day. Format the cells
as [h]:mm:ss

Then in B2 you can enter the formula:

=COUNTIF(time_range,">"&A1)-COUNTIF(time_range,">="&A2)

where time_range is your range on the other sheet, eg Sheet1!E$2:E
$1000

Then copy this formula down.

Hope this helps.

Pete

 

[Stefi]

Another solution: Your call times being in column A, build a table in column
B like Mike's column C starting from C2 (C1 is a header), select range C2:Cn
where n is the row No of last item in Column B plus 1, enter this formula
=FREQUENCY(A:A,B2:Bn)
and confirm it with Ctrl+Shift+Enter as an array formula!

Regards,
Stefi




„Mike H†ezt írta:

 

[Pete_UK]

Sorry, the first part of the formula should be ">=" rather than just
">".

Pete

Call data usually covers the calls you make, rather than calls
received.

It would be better to draw up a list of 15minute intervals on a
separate sheet - perhaps starting in A1. You can do this by entering
00:00:00 in A1, then in A2 entering this formula:

=A1+TIME(0,15,0)

and copying this down the column to cover a full day. Format the cells
as [h]:mm:ss

Then in B2 you can enter the formula:

=COUNTIF(time_range,">"&A1)-COUNTIF(time_range,">="&A2)

where time_range is your range on the other sheet, eg Sheet1!E$2:E
$1000

Then copy this formula down.

Hope this helps.

Pete

i have a list of telephone calls recieved with the time of the call over a 1
day (8hour) period. i need to report how many calls recieved in 15 min
intervals. useing excel 2003- Hide quoted text -

- Show quoted text -

 

[Andrew@rushington]

thanks for that after some jiggery pokery with formatting time (nightmare) it
has solved the problem

Cheers