Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank using two different categories within a multiple time period
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank using two different categories within a multiple time period
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank using two different categories within a multiple time per
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank using two different categories within a multiple time per
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank using two different categories within a multiple time per
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculating the sum of different categories over a given time peri | Excel Worksheet Functions | |||
modifying time categories | Excel Discussion (Misc queries) | |||
average rate of change per given time period between 2 moments in time of a value | Excel Worksheet Functions | |||
Every time i put a number with period it becomes a date and time | Excel Discussion (Misc queries) | |||
assign value for period of time | Excel Discussion (Misc queries) |