Help with Ranking Formula

[Mike11212]

Hello, I am trying to rank people according to their output. Below is a sample of what my spreadsheet looks like.

Name	Pick	Pick Rank	Pick %	Pick % Rank	Time	Time Rank	Power Rank
Joe	20	3	100%	1	2:15	3	1
Bob	113	1	98%	3	2.09	1	3
Chris	33	2	99%	2	2:11	2	2

<tbody>
</tbody>

Here is my problem, Bob is more productive, however his Pick% is lower. However although he is the top performer he is ranked lowest in the Power Rank. How can I fix this so that he is ranked higher? The power rank right now adds the Pick Rank, The Pick% Rank and the Time Rank. The higher the total of those three gives them the better rank.

I really would like to add a penalty. Example if their pick is less than 30 it subtracts points, if their pick is over 50 it adds points. But I am unsure how to do this. Any help would be great. Thank you in advance.

 

[ace19852]

May I suggest an alternative i.e. using and efficiency column J

My layout is

A1 = Name - A2 Text
B1 = Pick - B2 Numeric
C1 = Pick Rank - C2 =RANK(B2,$B$2:$B$4,0)
D1 = Pick % - D2 Numeric %
E1 = Pick % Rank - E2 =RANK(D2,$D$2:$D$4,0)
F1 = Time F2 Time
G1 = Time Rank G2 =RANK(F2,$F$2:$F$4,1)
H1 = Power Sum which you said above is Pick Rank, The Pick% Rank and the Time Rank C1+E1+G1

However I propose
H2 =C2+E2+G2+MAX($J$2:$J$4)/(F2/B2)

I1 = Power Rank - I2 =RANK(H2,$H$2:$H$4,0)

New
J1 = Efficiency - J2 =F2/B2

This then gets you closer without weighting with a penalty but is based on efficiency although that can be subjective as well and dependent on the range and size of products i.e if you only pick small items that are near to you it takes less time than if you only pick large items that are far away.

Hopefully there is and even mix of large vs small, far vs large or heavy vs light items

Cheers

 

[Mike11212]

May I suggest an alternative i.e. using and efficiency column J

My layout is

A1 = Name - A2 Text
B1 = Pick - B2 Numeric
C1 = Pick Rank - C2 =RANK(B2,$B$2:$B$4,0)
D1 = Pick % - D2 Numeric %
E1 = Pick % Rank - E2 =RANK(D2,$D$2:$D$4,0)
F1 = Time F2 Time
G1 = Time Rank G2 =RANK(F2,$F$2:$F$4,1)
H1 = Power Sum which you said above is Pick Rank, The Pick% Rank and the Time Rank C1+E1+G1

However I propose
H2 =C2+E2+G2+MAX($J$2:$J$4)/(F2/B2)

I1 = Power Rank - I2 =RANK(H2,$H$2:$H$4,0)

New
J1 = Efficiency - J2 =F2/B2

This then gets you closer without weighting with a penalty but is based on efficiency although that can be subjective as well and dependent on the range and size of products i.e if you only pick small items that are near to you it takes less time than if you only pick large items that are far away.

Hopefully there is and even mix of large vs small, far vs large or heavy vs light items

Cheers

Thank you for your help, will try it out as it seems it would work. I originally wanted the penalty type system because believe it or not, people try to game the system by purposely picking a small amount of items to keep the % high. Percentage is messuring their accuracy.