Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I Rank on a second criteria if the first is equal?
i am trying to rank sets of data on units ordered. Where the returned values
are the same i then need to rank on units delivered. I.E. Over the 88 regions in question the ordered units for the quarter in some cases are the same. I don't want to end up with some regions being ranked the same so i need a kind of RANKIF option, so IF the RANK is the same as any other then a second RANK based on a second criteria (in this case units delivered) needs to be considered. Maybe an easier example is to consider football, where teams are ranked by the points they have accumulated. If the points are the same they are then ranked on the goal difference. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I Rank on a second criteria if the first is equal?
you can order data by up to 3 criteria using data, sort. -- robert111 ------------------------------------------------------------------------ robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996 View this thread: http://www.excelforum.com/showthread...hreadid=554936 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I Rank on a second criteria if the first is equal?
Hi!
I'm not sure it's directly feasible... However - and fortunetaly - there's a plan B. If you're first criteria is in column B and the 2nd criteria in column C, add a column before using the TANK function with the following formula: B2+C2/1000000 (add some 0 if the column C has numbers larger than in column B). You now can use the RANK function on the new column... Does it help? -- KR V. "CohenRB" wrote: i am trying to rank sets of data on units ordered. Where the returned values are the same i then need to rank on units delivered. I.E. Over the 88 regions in question the ordered units for the quarter in some cases are the same. I don't want to end up with some regions being ranked the same so i need a kind of RANKIF option, so IF the RANK is the same as any other then a second RANK based on a second criteria (in this case units delivered) needs to be considered. Maybe an easier example is to consider football, where teams are ranked by the points they have accumulated. If the points are the same they are then ranked on the goal difference. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I Rank on a second criteria if the first is equal?
This of RANK as counting the number of entries that are the entry you're
looking at. Breaking a tie means counting the number of entries that are equal to the current entry, but have some other field whose value is greater than the current entry's. That's roughly the logic behind this formula: =COUNTIF($B$1:$B$13,""&B1)+1+SUMPRODUCT(--($B$1:$B$13=B1),--($C$1:$C$13C1)) (that's for row 1; autofill into subsequent rows) I've assumed that the data is in rows 1:13, with the primary criterion in column B and secondary in column C. "CohenRB" wrote: i am trying to rank sets of data on units ordered. Where the returned values are the same i then need to rank on units delivered. I.E. Over the 88 regions in question the ordered units for the quarter in some cases are the same. I don't want to end up with some regions being ranked the same so i need a kind of RANKIF option, so IF the RANK is the same as any other then a second RANK based on a second criteria (in this case units delivered) needs to be considered. Maybe an easier example is to consider football, where teams are ranked by the points they have accumulated. If the points are the same they are then ranked on the goal difference. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to sumif in excel with a not equal criteria | New Users to Excel | |||
To Rank on two Criteria | Excel Worksheet Functions | |||
Need Advanced Filter with NOT equal string and OR criteria | Excel Discussion (Misc queries) | |||
Rank based on criteria | Excel Worksheet Functions | |||
How do I setup criteria in DSUM to sum values NOT equal (<>) | Excel Worksheet Functions |