# Excel 2010 Rank.EQ with Multiple Tiebreakers

#### Daniel_Joseph

##### New Member
 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.

### 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
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.

Replies
9
Views
190
Replies
3
Views
39
Replies
8
Views
679
Replies
1
Views
35
Replies
3
Views
88