See attached Excel doc for example.
I do some reporting for a contact centre and a report I'm trying to build looks at agents wrap targets. This report uses averages and prints a percentage for us to look at and whether they've met the weekly % target. However I'm struggling to make the overall % (under 'Summary') truly accurate as it takes into account any zeros.
For individual agents I need a similar formula to what I already use, but ignores zeros or blanks.
Any help would be appreciated.
summarize all the data in a single sheet day wise.
Use Pivot Table.... Life would be easier.
Shashank Mishra
Please hit "Add Reputation" Button if you liked the answer.
Any numbers of your expected solution would also be helpful
Also you are averaging averages for your team figure which is usually considered bad practice
http://ksrowell.com/blog-visualizing...urate-hint-no/
Why not just sum all the calls for the person and the over wraps (sum(Calls)-sum(overwrap))/Sum (calls) is the figure you want to achieve?
You should have access to power pivot that can do what you ask more easily, I don't have it on this computer
Alternatively 1-(Sumif(Monday!a1:a100,[@Orange],Monday!c1:c100)+ same for each other day)/(Sumif(Monday!a1:a100,[@Orange],Monday!b1:b100)+ same for each other day)
will give you the correct mathematical answer
Last edited by davsth; 01-20-2020 at 06:51 AM.
Try using -->>>> IF(A7<>"",IFERROR(AVERAGE(IFERROR(SUMIFS(INDIRECT({"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday"}&"!D:D"),INDIRECT({"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday"}&"!A:A"),A7),"NA")),"NA"),"")
Hey this seems to work perfectly for the most part. However Agent6 is showing NA even though they have call data for Tuesday. I've had a similar issue when applying this to the actual report, in that it's showing NA for some agents despite having data for at least one day.
Any advice?
Capture.JPG
Not sure what are you referring to, here I am getting values for Agent6, Agent13 and Agent16 are NA
So I've attached what I'm currently looking at.
Agents 18,19, and 26 shouldn't be showing as NA as they have call data from one of the days. See what I mean?
you made some error in the formula, have fixed it. Only 18 and 26 has the data.
Can I ask what error I made? Whenever I go into the formula and press enter, 18 returns to the NA state and can't figure out why.
Try using CTRL+Shift+Enter
Awesome thank you very much!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks