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!

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

Rick


"KellyC" wrote in message
...
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!

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)" wrote:

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

Rick


"KellyC" wrote in message
...
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!

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" wrote in message
...
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)" wrote:

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

Rick


"KellyC" wrote in message
...
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!

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


"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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)" wrote:

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

Rick


"KellyC" wrote in message
...
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!

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

"KellyC" wrote:

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


"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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)" wrote:

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

Rick


"KellyC" wrote in message
...
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!