Complicated If Then (possibly?)

w0725610 · Aug 16, 2011

I am currently trying to design a spreadsheet to keep track of employee metrics for a call center. I have six different employees, and I'm keeping track of the percentage of total calls that each employee takes over the course of a given week. It looks something like this:

A|...B...|..C.|...D..|..E...|...F...|...G...|...H...|....I....|.......J........
1|Staff.|.Bill.|.8/8.|.8/9.|.8/10.|.8/11.|.8/12.|.Total.|.% Calls Answered
2|.Calls.|.....|..21.|.16..|..19...|..25...|..14...|...95...| 22%

This is a bit simplified, but it should be sufficient. Now obviously, the percentage of total calls is based on a total of all calls taken by all six employees in the department. The problem I'm running into is that I would like to make another column that keeps track of the % of calls answered, adjusted for days off. If an employee is out of the office for two days during the week, I want to know quickly and easily how efficiently they worked on the days that they were present, without handicapping their numbers based on the fact that they weren't in the office for a couple of days. I was thinking that this might be possible with some complicated if-then process, but I feel like it should be more simple than that. Any advice on a possible solution? Thanks in advance!

gnrboyd · Aug 17, 2011

Your post seems to indicate you'd like to make this a simple. I would think a simple average formula would give you what you need. If a cell is blank, the average formula doesn't count it. So if you had 20 calls in just 2 days of work, your average would be 10 (20/2 days) and not 4 (20/5 days). If you enter 0 in the cells, it is counted. So if you had 10,0,0,0,0 the average would be 2 (10/5 days). So on a day the employee is not there, just leave the cell blank. If they are there and do not take any calls, enter 0.

Anyway, as you've determined, the percentage of calls per user doesn't really tell you much without factoring in how much time they were at work. For that reason, a simple average formula would tell you the average number of calls per day and I think that is a better (simpler) way to look at it rather than percentages. (Just my opinion)

However, this doesn't factor in partial days of work but I am not sure if that is a concern of yours since it wasn't mentioned it in your post.

Hope this helps.

Book1