Non working team ranking needs help

[worthm]

Hello,
I'm using Office 2010 and I got some advice on ranking on one of these forums and I'm having a problem with the logic. See attachment. The sheet should rank each team mate on the five metrics on the attached sheet and give one overall rank number for each team mate. Most of the code is in the blue section to the right on the attachment. It is returning NA instead of a rank number.

Also, is there any way to have it ignore rows if any field in that row has a divided by zero error? Some of my team mates don't yet have audit scores so I wish to ignore them and I don't want to re-invent the wheel each time I add another employee

I don't really understand how this code works so I'm at a loss fixing it. Thanks for your help

[Glenn Kennedy]

It wasn't clear whether or not you wanted them ranked in ascending or descending order: so here are both alternatives. There were some really odd looking formulas being applied - which I've simplified. If this is NOT what you want, please explain what you want and the rules that apply.

[worthm]

Thank you Glenn, that's a step in the right direction but I think it's only part of the picture. It appears that you're only ranking based on the Audit % YTD column, column D. I need to rank each CSR based on all five metrics. Here is some additional info:

Audit % YTD - higher is better
Total # of calls YTD - higher is better
FNOL#s - higher is better
Status Calls #s - higher is better
Avg handling time - lower is better

is there a way to rank each CSR based on all five metrics with a single rank number?

Thanks again for your help

[Feastie]

Your #N/A result in column I is due to the calculation adding the entire row of data from L to Q, which is zero. You're then trying to find where zero comes in a column of numbers; which it can't do, hence #N/A.
If I understand your requirement you need the inverse of the sum of ranks (as lowest in this case is the best), therefore column L should read: =1/SUM(M7:Q7).
You then change the formula in I to read: =RANK(L7,L7:L18) with the appropriate anchors to fix the range.

[worthm]

Thanks Feastie
I think I understand what you're saying. Ive plugged it into the dashboard and the results make sense but can you take a look? Ive attached it again. The only changes Ive made are in columns I and L which Ive colored green. I can add the new hires later once they have meaningful data

[Glenn Kennedy]

That is what I had done. I have changed one column of formulas to make it simpler. Basically, I ranking them on the basis of the content of column L, which was an aggregate of columns M-Q (Col Q being ranked in the opposite direction to the others.

[worthm]

Thank you for your help Glenn!

[Glenn Kennedy]

Glad to have helped! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.