I didn’t want to hijack the other thread, and this is not a homework helper question, but rather a work thing. My boss emailed me a request earlier tonight but I’ve been busy around the house and haven’t put more than 30 minutes into this. But I’m a bit afraid to admit I’m stumped. Here goes:
I work in a call center. I have 15 agents on my team. One of the big stats my boss’ boss wants to see in one column are 1/ that all calls are answered within 30 seconds (if not; it’s an auto fail). He also wants to see that this stat doesn’t fall below 85% success rate.
I’m not certain how to do this. I think that I’m going to need at least one column for a data set of at least 25 calls per agent (for seconds it took each call to be answered) , one column to count the total # of calls per agent, and a formula to average this set to a single percentage. Then I have to do this per each agent.
It seems that my boss think’s that this is a simple request, but I think it’s going to get more complicated than she wants it to be. I tried thinking about the SUM function, but I can’t quite get the cell range to figure this out. I tried to do a google help search on this, but even the help page turned into a tough read.
Here’s my sort-of result: Column A is each agent’s name. Simply enter the seconds per call (let’s call it column B), and column C will be a if/then statement that if the call exceeds 30 seconds it’s a point. Total # of points divide by total # of calls equals a percentage. Below 85% is failure, and that’s column D.
So I don’t think this will be as easy as my boss thinks. I’m looking at data entry for about 25 calls per agent, and doing this for 15 agents. Then doing this for 8 teams. That’s a lot of data to input on a daily basis. Or am I making this harder than I need it to be?
I’m not sure I understand completely*, so can’t give a definitive answer, but I would look into:
=COUNTIF(), e.g. =COUNTIF(range,">30")
You might also look into array formulas, which are inputted by pushing Ctrl+Shift+Enter, which will add curly brackets around the formula, { and }, if inputted correctly.
=VLOOKUP() might be useable somehow. It depends on how the data is structured*, do you you want column C to have a lot of blank space, and only do the calculation for the first name appearing on the row?
Finally, you could use a Pivot Table, which might be easiest.
*Is column A like:
Bob Smith
Bob Smith
Bob Smith
etc.
Jane Jones
etc.?
Definitely use a pivot table; it has the advantage that you can extend your analysis later if (or more likely when) your boss changes the parameters of the request.
Looking at your initial post, 25 calls per agent per day, 15 agents per team, 8 teams means you’re looking at 3000 rows of data per day. That is indeed quite a lot of data to manually enter on a daily basis. How do you collect this data from other teams, and how much data would you need to keep in one place? One sheet per month, or more? There’s a point at which Excel will struggle to cope, but depending on which version of Excel you have, this could well be feasible.
My immediate feeling is that trying to keep the data in a single sheet will give you more analysis possibilities - the following headers suggest themselves
Team Name; Agent Name; Time to Answer; Success (as above)
This will allow you to analyse not just individuals, but also compare teams if required. The problem with giving bosses the power of analysis is that they always want more, so be prepared for this!
If you let us know which version of Excel you use, and how the data is collected, we might be able to give you more specific advice on how to manage the process more efficiently.
Is this a one-time deal, or something that will need to be updated on a regular basis?
If it’s just a one-time deal, a pivot table is OK. The problem with pivot tables is that they don’t automatically update when you enter new information. So if this is a situation where you are entering data for 25 calls* per day,* and you want to keep a running total, a pivot table would not be a good choice (IMO, pivot tables are almost never a good choice, but that’s my personal preference.)
[ol]
[li]Ensure that the header row are formatted differently than the rest of the rows (e.g. bold)… this helps Excel identify the table headers.[/li][li]Select your data including the header row[/li][li]Choose Insert/Table. Excel will have checked the option “My table has headers”. Click Ok[/li][li]Choose Insert/Pivot Table. Excel will have the data range as “Table1”. In the bottom section “Choose where you want the…” pick the top left cell of an open area on the existing sheet, or choose “New Worksheet”. Click Ok[/li][li]The Pivot Table Field List appears:[/li]Drag “Team” into Row Labels
Drag “Name” into Row Labels below Team
Drag “Result” into Col Labels
Drag “Seconds” into Values
[li]In the Values area of the Pivot Table Field List click the drop-down arrow beside “Sum of Seconds” field:[/li]Choose Value Field Settings
For the tab “Summarize Values by” change “Sum” to “Count”
For the tab “Show Values as” choose “% of row total”
Click Ok
[/ol]
This series of articles was helpful for me when I began designing my call center dashboard in Excel. You’ll probably want to track different KPIs but it should give you some good ideas about how to manipulate and present the data.
IMHO your biggest problem is not the analysis, it’s collecting the data.
Are you keying off a paper record of phone logs?
Is there the option for the operators to key their calls into a database or even better (much much bettter) an extract from the comms system logs?
Once you have the data in softcopy form the rest is straight forward.
I’m firmly in the “use a pivot table” school, unless you are familiar with arrays.
The instructions from K364 are spot on for a specific data set.
You’ll need to extend this range after every data entry session, and then refresh the pivot table.
Alternatively I’d change the pivot table’s data range ‘data sheet’!$A:$E
Note that for this to work your titles must be in row 1.
This will include in the pivot range a lot of blank rows, which you’ll want to exclude .
Therefore to the fields suggested by Dervorin I’d add as ColE the formula =if(A2="",“Invalid”,“Valid”) with a title something like Include?
Then add the field Include? to the pivot table as a report filter selecting the “Valid” rows.
You’ll still need to refresh the pivot table after you’ve completed data entry.
At this stage the bottleneck is the data entry capability, and rekeying 3,000 lines per day has hairs all over it for any number of reasons including your sanity and data quality.
You may be able to run the pivot table using the comms log as the external data source or as a Plan B append some daily/weekly extract (say a CSV file) into a database and then run the pivot table directly on the database.
I didn’t have time this morning to fully explain… but your concern is handling by first converting the data entry range into a table (step 3).
If the data entry range is a table, new rows can be added by simply starting to type in the first row below the table. After the rows are added to the table, refresh the pivot table by right-clicking on it and choosing “Refresh”
I think we’re going to get numbers every day, so we’re talking about a lot of data. I need to figure out if each supervisor (me included) is going to have to key our own data, or if the boss’ assistant is going to have to do it.
Can’t your ACD system track maximum waiting time for you? Back when my group was using Symposium, I could slice and dice KPIs any way to Sunday with its reporting tools,
