So I am working on a spreadsheet that can tell me the overall performance of an agent. In this Scenario the agents Quality score makes up 75% of their overall performance and Average Handle times makes up 12.5% and Adherence makes up the other 12.5%. What formula would accurately tell me the overall efficiency score (AHT and Adherence) combined.
AHT = 450 seconds
Adherence =99%
These two numbers should be 25% of the overall score.
I would think you'd need to know what the AHT value's percentage is as an expected Handle time. Is 450 seconds 50%? Is it 95%?
You would simply need to multiply the AHT percentage and Adherence percentage by .125 (12.5%) and then add the results together.
(AHT_Percent * 0.125)+(Adherence_Percent * 0.125) = x% (25% would be max if both AHT and Adherence were at 100%)
That is what I was thinking also but it doesn't seem to be working out correctly. If you look at the attached excel spreadsheet an on the tab labeled "Patty Burrum" with Adherence of 100% in cell G16 and AHT 100% in cell H15 and Quality 100% in cell G6 I would think the total in cell G4 would be 100%?Originally Posted by Paul
The issue is because the values in G6 and G16 are whole numbers, but the value in H15 is a percentage. Percentages are decimal values between 0 and 1 (0% and 100%). So 100% actually equals 1, and when added to 50 equals 51.
51 * 0.25 equals 12.75, which when added to 75% from the Quality gives 87.75% (or 88% rounded).
Try adjusting your formula in cell G8 to:
=IFERROR((H15*100*0.50)+(G16*0.5),"")
or simply
=IFERROR((H15*50)+(G16*0.5),"")
Thank You so much, I also think I figured it out by making sure they were all % which is what they are and it works correct as well. Thanks for alerting me to that..
Paul,
The other issue I can't figure out is on the same tab "Patty Burrum" cell I6 which pulls its data from the December Roll Up tab in field K6. When it is blank it is reading as a zero (0). I have tried to use =IFERROR(('December Team RollUp'!K6),"") to stop it from doing that but it isn't working. Am I using the wrong format/formula?
You should be able to use either:
=IF(ISBLANK('December Team RollUp'!K6),"",'December Team RollUp'!K6)
or
=IF('December Team RollUp'!K6="","",'December Team RollUp'!K6)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks