Rank

[lysol]

Hi everyone,

i have to track the performance of some sales Reps, p

All performance is measured on a composite basis with sales performance representing 50% (the higher the better), Customer Feedback 30% (the higher the better), and Average time spent per account 20% (the lower the better).

any ideas on how i can do this? on excel 2010?

[shg]

Welcome to the board.

Post a workbook to show your data layout and an example of what you want.

[lysol]

Thanks for the reply.
I'm probably not allowed to post the data for confidentiality reasons, basically I have a pivot table,
Where

% of Sales Goal % of Customer Feedback Goal Time spent per account
REP A 150% 99% 300mins
REP B 80% 97 % 200mins

Performance has a weighted rank – 50% goes to the sales goal - 30% customer feedback – 20% efficiency (time spent per account)

I’m thinking about making a field in the pivot table with the formula

(.5 x % of sales goal) + (.3 x % of Customer feedback goal) ….. would this even work? And how would I do the time spent per account since it’s not in a percentage and the lowest time spent should get the highest rank.

Thanks in advance!

[shg]

How those get combined to an overall rating could be done a zillion ways. Here's one.

       -A-- --B--- ---C--- -D-- --E--- --F--- G ---------------------H---------------------
   1           50%     30%         20%                                                     
   2   Rep  %Sales %Fdback Time T Rank Rating                                              
   3   Alan    87%     95%  247    13%    75%   E3 and down: =1-PERCENTRANK($D$3:$D$11, D3)
   4   Barb    75%     66%  207    50%    67%   F3 and down: =SUMPRODUCT($B$1:$E$1, B3:E3) 
   5   Cain   117%     84%  194    63%    96%                                              
   6   Dana   113%     99%  216    25%    91%                                              
   7   Eric   125%     81%  186    75%   102%                                              
   8   Fran   135%     66%  213    38%    95%                                              
   9   Gary   146%     84%  152    88%   116%                                              
  10   Hana    82%     81%  293     0%    65%                                              
  11   Ivan    96%     97%  107   100%    97%

[lysol]

thank you!

the problem is that i can't get the time spent per account column as a percentage, it's either 200mins or 300 mins, etc. did you just have the lowest score equal 100%?

[shg]

The formula in E3 is in my post.