Rank using two different categories within a multiple time period

[Mimi]

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!

 

[Fred Smith]

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]

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?

 

[Fred Smith]

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]

Thanks a lot for your help Fred!
--
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.