Excel 2010 Rank.EQ with Multiple Tiebreakers

[Daniel_Joseph]

[TABLE="width: 384"]
<colgroup><col width="64" style="width:48pt"> <col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]Cell[/TD]
[TD="class: xl63, width: 64"]A[/TD]
[TD="class: xl63, width: 64"]B[/TD]
[TD="class: xl63, width: 64"]C[/TD]
[TD="class: xl63, width: 64"]D[/TD]
[TD="class: xl63, width: 64"]E[/TD]
[/TR]
[TR]
[TD="class: xl63"]1[/TD]
[TD]Rank[/TD]
[TD]Name[/TD]
[TD]Criteria1[/TD]
[TD]Criteria2[/TD]
[TD]Criteria3[/TD]
[/TR]
[TR]
[TD="class: xl63"]2[/TD]
[TD][/TD]
[TD]Ben[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD="class: xl63"]3[/TD]
[TD][/TD]
[TD]Ray[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]36[/TD]
[/TR]
[TR]
[TD="class: xl63"]4[/TD]
[TD][/TD]
[TD]Steve[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl63"]5[/TD]
[TD][/TD]
[TD]Bob[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]-2
[/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[Marcelo Branco]

Not sure if I understand what you want. Maybe...


[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td]

Rank​

[/td][td]

Name​

[/td][td]

Criteria1​

[/td][td]

Criteria2​

[/td][td]

Criteria3​

[/td][/tr]
[tr][td]

2​

[/td][td]

1​

[/td][td]

Ben​

[/td][td]

1​

[/td][td]

0​

[/td][td]

21​

[/td][/tr]
[tr][td]

3​

[/td][td]

3​

[/td][td]

Ray​

[/td][td]

2​

[/td][td]

2​

[/td][td]

36​

[/td][/tr]
[tr][td]

4​

[/td][td]

2​

[/td][td]

Steve​

[/td][td]

2​

[/td][td]

1​

[/td][td]

6​

[/td][/tr]
[tr][td]

5​

[/td][td]

4​

[/td][td]

Bob​

[/td][td]

2​

[/td][td]

2​

[/td][td]

-2​

[/td][/tr]
[/table]

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.