Daniel_Joseph
New Member
- Joined
- Oct 7, 2014
- Messages
- 1
| Cell | A | B | C | D | E |
| 1 | Rank | Name | Criteria1 | Criteria2 | Criteria3 |
| 2 | Ben | 1 | 0 | 21 | |
| 3 | Ray | 2 | 1 | 36 | |
| 4 | Steve | 2 | 1 | 6 | |
| 5 | Bob | 2 | 2 | -2 |
<colgroup><col width="64" style="width:48pt"> <col width="64" span="5" style="width:48pt"> </colgroup><tbody>
</tbody>
Right now, the formula in column A that determines rankings is RANK.EQ($C3, $C$3:$C$6, 1)+COUNTIFS($C$3:$C$6, $C3, $E$3:$E$6, ">"&$E3)
Essentially, it ranks based on criteria one in ascending order, then uses criteria three as a tiebreak where a larger value is considered better (36 wins the tiebreak over 6)
What I want is for criteria two to be the first tiebreak (also in ascending order), and criteria three to be the third tiebreak
So, if Ray's line read 2 | 2 | 36 |, then Ray would rank third behind Ben and Steve but ahead of Bob.
Have tried some stuff with SUMPRODUCT but haven't gotten any luck so far.
Excel really should have a RANK.TBK function that allows the user to specify columns to be used in the event of a tie, and if the larger or smaller value wins the tie. if anyone knows of or has written a VBA module for such a function it would also be greatly appreciated.
