Excel 2010 Rank.EQ with Multiple Tiebreakers

Daniel_Joseph

New Member
Joined
Oct 7, 2014
Messages
1
CellABCDE
1RankNameCriteria1Criteria2Criteria3
2Ben1021
3Ray2136
4Steve216
5Bob22-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.
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,393
Not sure if I understand what you want. Maybe...


A
B
C
D
E
1
Rank​
Name​
Criteria1​
Criteria2​
Criteria3​
2
1​
Ben​
1​
0​
21​
3
3​
Ray​
2​
2​
36​
4
2​
Steve​
2​
1​
6​
5
4​
Bob​
2​
2​
-2​

Formula in A2 copied down
=RANK.EQ($C2, $C$2:$C$5, 1)+SUMPRODUCT(--($C$2:$C$5=$C2), --($D$2:$D$5-$E$2:$E$5/(10^6)<$D2-E2/(10^6)))

Hope this helps

M.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,045
Messages
5,526,447
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top