Complicated If Then (possibly?)

w0725610

New Member
Joined
Aug 16, 2011
Messages
1
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!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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
ABCDEFGHI
1Staff8/88/98/108/118/12Total%TotalCallsAnsweredAverageCallsAnswered
2Bill21161925149522%19
3Sara14171218198019%16
4Tom12272606515%16.25
5Linda1413171196415%12.8
6Bob22172314199522%19
7Beth1914338%16.5
8Totals8390978775432100%16.61538462
Sheet2
 
Upvote 0

Forum statistics

Threads
1,224,587
Messages
6,179,734
Members
452,939
Latest member
WCrawford

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top