call distribution
 

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

call distribution
 

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

"Andrew@rushington" wrote:

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

call distribution
 

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

On Nov 22, 11:10 am, Andrew@rushington
wrote:
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

call distribution
 

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:

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

"Andrew@rushington" wrote:

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

call distribution
 

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

Pete

On Nov 22, 11:57 am, Pete_UK wrote:
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

On Nov 22, 11:10 am, Andrew@rushington



wrote:
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 -

call distribution
 

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

Cheers

"Mike H" wrote:

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

"Andrew@rushington" wrote:

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