I'm using Excel 2003. I need to calculate the ranking for a group of
employees based on two different categories: sales $ amount and percentage
grade. These two categories are monthly. The two categories have a 50%
weighted average. I need to obtain the overall ranking per employee based on
these two categories for the four month period. Here is an ilustration of
what the data looks like:

J $ J % F $ F% M $ M% A $ A %
EE1 $9 85% $8 83% $7 83% $7 84%
EE2 $7 82% $8 85% $7 80% $8 84%
EE3 $8 81% $8 81% $7 81% $7 80%

Thanks in advance for helping me!
--
Thank you

I would do this in two steps: total the amounts for the period, and then
rank them. Add two columns (J and K) for the totals:
=sum(b2,d2,f2,h2)
=average(c2,e2,g2,i2)

=RANK(J2,$J$2:$J$4,0)*50%+RANK(K2,$K$2:$K$4,0)*50%

Regards,
Fred.

"Mimi" wrote in message
...
I'm using Excel 2003. I need to calculate the ranking for a group of
employees based on two different categories: sales $ amount and percentage
grade. These two categories are monthly. The two categories have a 50%
weighted average. I need to obtain the overall ranking per employee based
on
these two categories for the four month period. Here is an ilustration of
what the data looks like:

J $ J % F $ F% M $ M% A $ A %
EE1 $9 85% $8 83% $7 83% $7 84%
EE2 $7 82% $8 85% $7 80% $8 84%
EE3 $8 81% $8 81% $7 81% $7 80%

Thanks in advance for helping me!
--
Thank you

Thanks Fred, can you also tell me how would you handle if you had to
calculate a monthly rank and then use the monthly ranking to get an overall
ranking for the period? Would both method render the same results?
--
Thank you


"Fred Smith" wrote:

I would do this in two steps: total the amounts for the period, and then
rank them. Add two columns (J and K) for the totals:
=sum(b2,d2,f2,h2)
=average(c2,e2,g2,i2)

=RANK(J2,$J$2:$J$4,0)*50%+RANK(K2,$K$2:$K$4,0)*50%

Regards,
Fred.

"Mimi" wrote in message
...
I'm using Excel 2003. I need to calculate the ranking for a group of
employees based on two different categories: sales $ amount and percentage
grade. These two categories are monthly. The two categories have a 50%
weighted average. I need to obtain the overall ranking per employee based
on
these two categories for the four month period. Here is an ilustration of
what the data looks like:

J $ J % F $ F% M $ M% A $ A %
EE1 $9 85% $8 83% $7 83% $7 84%
EE2 $7 82% $8 85% $7 80% $8 84%
EE3 $8 81% $8 81% $7 81% $7 80%

Thanks in advance for helping me!
--
Thank you

For monthly rankings, just use the ranking formula on each month's data.

To rank based on the monthly rankings, average them for the period, then
rank them.

This method won't always produce the same results as your first request. You
could have someone with abnormally high sales in one month, but poor sales
in the other three. Under the first method, they would rank first in sales,
but using the second, they would not.

Regards,
Fred.

"Mimi" wrote in message
...
Thanks Fred, can you also tell me how would you handle if you had to
calculate a monthly rank and then use the monthly ranking to get an
overall
ranking for the period? Would both method render the same results?
--
Thank you


"Fred Smith" wrote:

I would do this in two steps: total the amounts for the period, and then
rank them. Add two columns (J and K) for the totals:
=sum(b2,d2,f2,h2)
=average(c2,e2,g2,i2)

=RANK(J2,$J$2:$J$4,0)*50%+RANK(K2,$K$2:$K$4,0)*50%

Regards,
Fred.

"Mimi" wrote in message
...
I'm using Excel 2003. I need to calculate the ranking for a group of
employees based on two different categories: sales $ amount and
percentage
grade. These two categories are monthly. The two categories have a 50%
weighted average. I need to obtain the overall ranking per employee
based
on
these two categories for the four month period. Here is an ilustration
of
what the data looks like:

J $ J % F $ F% M $ M% A $ A %
EE1 $9 85% $8 83% $7 83% $7 84%
EE2 $7 82% $8 85% $7 80% $8 84%
EE3 $8 81% $8 81% $7 81% $7 80%

Thanks in advance for helping me!
--
Thank you

Thanks a lot for your help Fred!
--
Thank you


"Fred Smith" wrote:

For monthly rankings, just use the ranking formula on each month's data.

To rank based on the monthly rankings, average them for the period, then
rank them.

This method won't always produce the same results as your first request. You
could have someone with abnormally high sales in one month, but poor sales
in the other three. Under the first method, they would rank first in sales,
but using the second, they would not.

Regards,
Fred.

"Mimi" wrote in message
...
Thanks Fred, can you also tell me how would you handle if you had to
calculate a monthly rank and then use the monthly ranking to get an
overall
ranking for the period? Would both method render the same results?
--
Thank you


"Fred Smith" wrote:

I would do this in two steps: total the amounts for the period, and then
rank them. Add two columns (J and K) for the totals:
=sum(b2,d2,f2,h2)
=average(c2,e2,g2,i2)

=RANK(J2,$J$2:$J$4,0)*50%+RANK(K2,$K$2:$K$4,0)*50%

Regards,
Fred.

"Mimi" wrote in message
...
I'm using Excel 2003. I need to calculate the ranking for a group of
employees based on two different categories: sales $ amount and
percentage
grade. These two categories are monthly. The two categories have a 50%
weighted average. I need to obtain the overall ranking per employee
based
on
these two categories for the four month period. Here is an ilustration
of
what the data looks like:

J $ J % F $ F% M $ M% A $ A %
EE1 $9 85% $8 83% $7 83% $7 84%
EE2 $7 82% $8 85% $7 80% $8 84%
EE3 $8 81% $8 81% $7 81% $7 80%

Thanks in advance for helping me!
--
Thank you