My manager has requested that I create a spreadsheet with a lot of information for our call centre. As a quick bit of background information, we're a call centre that receives approximately 1000-1500 calls daily, with anywhere from 14-20 people working the phones. It's your standard 9-5 job, just a larger scale numberwise than what i'm used to working with.

She wants to be able to enter in start time and stop times for calls and have the spreadsheet populate the call time and then also the average length of the calls.

This is where it gets fun. She wants to be able to see how many calls a representative takes during any given hour, and then the average length of those calls.

Example:

If Scott gets 13 calls between 8:00-9:00, averaging about 3 minutes a call, how can we get that data into a spreadsheet?

She intends on using these to see the efficiancy of the way we handle the calls and when we are most busy.


I have a general idea of how the spreadsheet might look, but I don't have a clue how to link up all the data, and since you guys have helped me in the past, I figured I might check with you.

I'm thinking that there's probably an easy way to figure out how long each call takes and then what the average length is, but I'm not sure how to section it off by the hour, and find the averages from those.

If it helps, as she put it, she wants to know, "how many calls Scott on average takes between 8-9, 9-10, 10-11, 11-12, on any given day (monday, tuesday, wednesday), and approximately how longs those calls take (per hour)." I believe she would also like a more general number for end of the day/week totals, but that shouldn't be an issue.


Any help, suggestion, information, or other ideas would all be appreciated.


thanks!