Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ranking on several fields
I have a spreadsheet that currently uses RANK to work out the best
performing salesperson in a team of 12. Column E ranks column D (sales of product 1) Column G ranks Column F (sales of product 2) Column I ranks column H (sales of product 3) Column K ranks column J (sales of product 4) Column M ranks Column L (sales of product 5) In column N I have totalled all the ranking values in E,G,I,K,M. This will often result in duplicate values, which causes big problems in column O, which is ranking column N. I need column O to be able to break the tie when it occurs, firstly using column D values, but if this still results in a tie, then referring to column F values (the bigger taking the lead). This should avoid the ties in column O and would be very rare to still result in a tie. Any ideas? Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ranking on several fields
consider sorting rather than ranking
-- Gary's Student gsnu200703 "Michael Chappell" wrote: I have a spreadsheet that currently uses RANK to work out the best performing salesperson in a team of 12. Column E ranks column D (sales of product 1) Column G ranks Column F (sales of product 2) Column I ranks column H (sales of product 3) Column K ranks column J (sales of product 4) Column M ranks Column L (sales of product 5) In column N I have totalled all the ranking values in E,G,I,K,M. This will often result in duplicate values, which causes big problems in column O, which is ranking column N. I need column O to be able to break the tie when it occurs, firstly using column D values, but if this still results in a tie, then referring to column F values (the bigger taking the lead). This should avoid the ties in column O and would be very rare to still result in a tie. Any ideas? Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ranking on several fields
I'm assuming you have 12 rows of data, say, D2:O13, and you're ranking
highest to lowest. By summing the individual ranks and then ranking that sum you are in effect reversing the the rank order from lowest to highest. Enter this formula in Q2 and copy down to Q13: =SUM(D2,F2,H2,J2,L2) Enter this formula in P2 and copy down to P13: =RANK(Q2,Q$2:Q$13,1) Compare the ranks in column P to the ranks you have in column O. You'll notice that they are the same with differences for any ties that might have been in column O. Biff "Michael Chappell" wrote in message ... I have a spreadsheet that currently uses RANK to work out the best performing salesperson in a team of 12. Column E ranks column D (sales of product 1) Column G ranks Column F (sales of product 2) Column I ranks column H (sales of product 3) Column K ranks column J (sales of product 4) Column M ranks Column L (sales of product 5) In column N I have totalled all the ranking values in E,G,I,K,M. This will often result in duplicate values, which causes big problems in column O, which is ranking column N. I need column O to be able to break the tie when it occurs, firstly using column D values, but if this still results in a tie, then referring to column F values (the bigger taking the lead). This should avoid the ties in column O and would be very rare to still result in a tie. Any ideas? Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ranking on several fields
In article , T. Valko
wrote: I'm assuming you have 12 rows of data, say, D2:O13, and you're ranking highest to lowest. By summing the individual ranks and then ranking that sum you are in effect reversing the the rank order from lowest to highest. Enter this formula in Q2 and copy down to Q13: =SUM(D2,F2,H2,J2,L2) Enter this formula in P2 and copy down to P13: =RANK(Q2,Q$2:Q$13,1) Compare the ranks in column P to the ranks you have in column O. You'll notice that they are the same with differences for any ties that might have been in column O. Biff Thanks Biff, That appears to be the kind of thing I'm looking for. Unfortunately, the reason I've ranked individually is that columns D and F have to show the actual *number* of items sold and columns H,J and L show the *monetary values* of items sold. Is there another way round it? If it's any help, I have put up the spreadsheet at: http://tinyurl.com/2r2fjf Michael |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ranking on several fields
That file really doesn't help. There's no raw data. What about that UDF?
Biff "Michael Chappell" wrote in message ... In article , T. Valko wrote: I'm assuming you have 12 rows of data, say, D2:O13, and you're ranking highest to lowest. By summing the individual ranks and then ranking that sum you are in effect reversing the the rank order from lowest to highest. Enter this formula in Q2 and copy down to Q13: =SUM(D2,F2,H2,J2,L2) Enter this formula in P2 and copy down to P13: =RANK(Q2,Q$2:Q$13,1) Compare the ranks in column P to the ranks you have in column O. You'll notice that they are the same with differences for any ties that might have been in column O. Biff Thanks Biff, That appears to be the kind of thing I'm looking for. Unfortunately, the reason I've ranked individually is that columns D and F have to show the actual *number* of items sold and columns H,J and L show the *monetary values* of items sold. Is there another way round it? If it's any help, I have put up the spreadsheet at: http://tinyurl.com/2r2fjf Michael |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ranking on several fields
In article ,
T. Valko wrote: That file really doesn't help. There's no raw data. What about that UDF? Biff UDF? Michael |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ranking on several fields
UDF?
User Defined Function The file looks like a demonstration file for a custom UDF that calculates ranks. Biff "Michael Chappell" wrote in message ... In article , T. Valko wrote: That file really doesn't help. There's no raw data. What about that UDF? Biff UDF? Michael |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ranking on several fields
So say you have 3 ranks, rank1 the most important, rank2 & rank3 to
break ties How about looking at: Rank = rank1 + rank2/1000 + rank3/1000000 (Assuming you've got <1000 sales guys...) On 4 Feb, 19:29, "T. Valko" wrote: UDF? User Defined Function The file looks like a demonstration file for a custom UDF that calculates ranks. Biff "Michael Chappell" wrote in message ... In article , T. Valko wrote: That file really doesn't help. There's no raw data. What about that UDF? Biff UDF? Michael |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I pivot multiple data fields and display them in a column? | Excel Discussion (Misc queries) | |||
ranking problem | Excel Worksheet Functions | |||
Need pie chart with number of fields instead of info in fields | Charts and Charting in Excel | |||
Number of dropdown fields in Excel is limited. I need more. How? | Excel Discussion (Misc queries) | |||
Sorting Spreadsheet with Merged Fields | Excel Discussion (Misc queries) |