Working out staff contribution as a percentage taking into account absences?

Welcome to the Forum highland_turkey!

A percentage is not the correct metric in this situation. You need to use a productivity metric, in this case average calls per hour of work. That will give you a measure of productivity to allow you to compare staff performance without regard as to how many hours they work. There is really no reasonable way to do that by using percentage of contribution to the total workload. I have attached a simple example. This example also calculates statistics and uses conditional formatting to highlight the most productive and least productive worker.

Another metric for call centers is call length, which you want to be short. Do you have a computerized system that collects that kind of information?

You said you created a spreadsheet. It will be much easier to understand your problem if you provide your file. This allows us to see your data, layout, code (if there is any), much easier than describing it in several paragraphs. You still have to explain what you want, but it makes the whole process easier. Once we understand the problem, having your file will let us experiment with your data, formulas, and code, and possibly attach a file with a completed solution.

To post a file:

1. Under the text box where you type your reply click the Go Advanced button.
2. In the next screen look above the reply box and click the paper click icon. You will get a pop-up screen.
3. In the pop-up, click Add then Select to select a file from your local drive
4. Upload it
5. Click Done to attach it.

It will be displayed underneath your post text. Alternatively as the last step you can click the Insert Inline button and a hyperlink to the file will be inserted directly into your post text in the spot where your cursor is set at the moment you click the button.

Originally Posted by highland_turkey

...my manager wanted to be able to tell staff a percentage so that they can give them an indication of how much work each staff member is doing compared to others in the site.

...an estimate of how much they couldve done if they were at work for 100% of the time.

Your file has no data or descriptions in it so I really don't know how you use it. But what you describe is normalizing the productivity of each person so they can be compared, which is exactly what productivity measures do.

If your boss insists on seeing a percentage, then you can calculate calls per hour for each person, and then calculate that as a percentage of the sum of all of those figures. Mathematically that's exactly the same thing as figuring out how many calls everyone would make if you scaled their time to all be 40 hours. See column F in the attached. I provided a formula in column F that does not require calculating a productivity column separately.

Hi,

Thanks for replying. Ive had a look at the formula you used on the example and Ive tried to recreate it on mine and just cant seem to get it to work for some reason?
Ive put in some data in my sheet so you can see how it works.
Each sheet is for each month, and all the months are added into the YTD (year to date). In the advisor columns it allows you to put in the number of calls each advisor did. And in the second section under calls it allows you to put in the number of calls for that advisors team, the command (normally a group of 5 or 6 teams) and then the whole contact centres calls.
Allowing to work out a percentage of each group.
On the team information page ive added a section to allow the manager to put in the total hours within a month a staff member works. Should the forumula be different if the spreadsheet uses different pages?
I would really appreciate if you could explain how I could get this to work, if you know.
Ive added a Calc column in the spreadsheet, the aim is to get a working version of the calculation into that column of the pages for each month and if possible the YTD sheet combining all stats so far. and then once I understand it and get it working to reformat the sheet into a better format so its easier to understand.

Thanks again for your help so far.


TL_team_percentage_overview_updated_1.xls