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!

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

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

"Rick Rothstein (MVP - VB)" wrote:

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

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!!



"KellyC" wrote:

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

"Rick Rothstein (MVP - VB)" wrote:

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

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


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



"KellyC" wrote:

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

"Rick Rothstein (MVP - VB)" wrote:

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