Rank Function with Multiple Criteria

[KellyC]

I am trying to Rank 5 different criteria for 28 employees. I am currently
ranking each criteria as a seperate column within each category; however, I
need to roll up all of the rankings to another page and have a Power Ranking
per employee. The Power Ranking was working as an AVERAGE function until the
percentages of the categories were not an equal 20%. Can anyone help?

Thank you!

 

[Rick Rothstein \(MVP - VB\)]

Sounds like you want a "weighted average". Multiply each ranking by its
percentage weight and then add them up.

Rick

 

[KellyC]

I have tried that and it doesn't work because the rankings are not
consistent; instead of the rankings showing 28 for last place, the function
is taking the next number. I think that is where my problem needs to be
fixed.

Employee Sales Sales 2 New Acct Pipeline Present. Power Rank
EE 1 11 7 6 7 1 6.90
EE2 1 1 2 11 19 7.00
EE3 5 3 6 2 17 7.05
EE4 11 7 6 14 3 8.45
EE5 11 7 6 17 2 8.65
EE6 4 2 6 7 23 8.70
EE7 2 7 6 11 20 9.10
EE8 11 7 6 4 12 9.20
EE9 11 7 6 19 3 9.20
EE10 11 7 6 6 11 9.25
EE11 7 7 6 7 17 9.50
EE12 11 7 6 14 9 9.95
EE13 11 7 6 14 10 10.20
EE14 11 7 6 19 7 10.20
EE15 6 4 2 1 28 10.25
EE16 11 7 6 26 3 10.25
EE17 11 7 6 22 6 10.40
EE18 11 7 6 19 8 10.45
EE19 3 7 2 22 21 11.35
EE20 11 7 6 17 14 11.65
EE21 8 5 1 11 24 11.70
EE22 9 7 6 4 25 11.75
EE23 10 6 5 2 26 11.80
EE24 11 7 6 7 22 12.15
EE25 11 7 6 22 14 12.40
EE26 11 7 6 26 13 12.75
EE27 11 7 6 26 16 13.50
EE28 11 7 6 22 27 15.65

 

[Rick Rothstein \(MVP - VB\)]

You gave us a lot of numbers, but you didn't tell us what the percentages
are for each of the categories. Also, it would help if you showed us the
formula you are using.

Rick

 

[KellyC]

Sorry. Hopefully this helps.

C1 - Sales 35%
C2 - Sales 2 25%
C3 - New Acct 0%
C4 - Pipeline 15%
C5 - Present 25%

=$C$1*C9+$C$2*D9+$C$3*E9+$C$4*F9+$C$5*G9

 

[KellyC]

The C, D, E, F and G 9 all represent the numbers I have listed below (ranking
from data page)

 

[Rick Rothstein \(MVP - VB\)]

I think I forgot to tell you to divide the final summation by the 5 (the
number of categories) as you wanted an average. I'm guessing if you did that
with the formula you showed us, it would work as you wanted. Here is a more
compact way to do the same thing...

=SUMPRODUCT(B9:F9*Sheet3!C$1:C$5)/5

using the same Row 9 data as your example formula did. Of course, this
formula can be copied down (and up) as necessary.

Rick

 

[KellyC]

Thank you!! Adding the dividing factor of 5 worked in my previous formula.
What does the Sheet3! stand for in your other example?

 

[KellyC]

I understand what you were doing with the Sheet 3. When I try the SUMPRODUCT
formula I get the same result as I was getting before adding the /5 on my
previous formula.

Thank you very much for your help!!

 

[Rick Rothstein \(MVP - VB\)]

Yes, the Sheet3 was a left over from my test on my system (I used Sheet3
because Sheet2 had other test data on it that I did not want to remove)
which I forgot to correct prior to posting my answer. As for the result from
the SUMPRODUCT formula... did you notice the /5 on the end of it? Anyway,
I'm glad we got your problem resolved.

Rick