Rank using 2 tie breaker columns

[rskip]

Hello, I am needing to rank a table that has columns for Names, Point Totals, Avg Race Position, and Avg Qualify Position.
The ranking should be by largest point totals, however if there is a tie, the first tie breaker would be by lowest avg race position, and if still a tie, by lowest qualify position. I am having troubles coming up with the formula in column B (Point Ranking) that will return a unique Point Ranking number.
Thanks
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Driver Name[/TD]
[TD][TABLE="width: 83"]
<tbody>[TR]
[TD="class: xl65, width: 83"]Point Ranking[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 82"]
<tbody>[TR]
[TD="class: xl65, width: 82"]Points Total[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 82"]
<tbody>[TR]
[TD="class: xl65, width: 82"]Avg Race Position[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 82"]
<tbody>[TR]
[TD="class: xl65, width: 82"]Avg Qual Postion[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Vance
[/TD]
[TD]1[/TD]
[TD]50[/TD]
[TD]1.0[/TD]
[TD]2.0[/TD]
[/TR]
[TR]
[TD]Ron[/TD]
[TD]2[/TD]
[TD]28[/TD]
[TD]3.5[/TD]
[TD]2.0[/TD]
[/TR]
[TR]
[TD]Darren[/TD]
[TD]3[/TD]
[TD]23[/TD]
[TD]4.5[/TD]
[TD]9.5[/TD]
[/TR]
[TR]
[TD]Scott[/TD]
[TD]4[/TD]
[TD]21[/TD]
[TD]5.0[/TD]
[TD]15.0[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]5[/TD]
[TD]20[/TD]
[TD]5.5[/TD]
[TD]9.0[/TD]
[/TR]
[TR]
[TD]Robert[/TD]
[TD]6[/TD]
[TD]14[/TD]
[TD]6.5[/TD]
[TD]9.0[/TD]
[/TR]
[TR]
[TD]Jesse[/TD]
[TD]7[/TD]
[TD]10[/TD]
[TD]5.0[/TD]
[TD]1.0[/TD]
[/TR]
[TR]
[TD]Randy[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]12.0[/TD]
[TD]4.5[/TD]
[/TR]
[TR]
[TD]William[/TD]
[TD]9[/TD]
[TD]8[/TD]
[TD]8.0[/TD]
[TD]10.0[/TD]
[/TR]
[TR]
[TD]Reid[/TD]
[TD]10[/TD]
[TD]6[/TD]
[TD]7.0[/TD]
[TD]5.0[/TD]
[/TR]
[TR]
[TD]Greg[/TD]
[TD]11[/TD]
[TD]2[/TD]
[TD]10.5[/TD]
[TD]4.5[/TD]
[/TR]
[TR]
[TD]Troy[/TD]
[TD]12[/TD]
[TD]2[/TD]
[TD]11.5[/TD]
[TD]14.5[/TD]
[/TR]
[TR]
[TD]Bernie[/TD]
[TD]13[/TD]
[TD]2[/TD]
[TD]13.5[/TD]
[TD]15.0
[/TD]
[/TR]
[TR]
[TD]Norman[/TD]
[TD]14[/TD]
[TD]2[/TD]
[TD]17.5[/TD]
[TD]10.5[/TD]
[/TR]
[TR]
[TD]CR[/TD]
[TD]15[/TD]
[TD]1[/TD]
[TD]11.0[/TD]
[TD]10.0[/TD]
[/TR]
[TR]
[TD]Daniel[/TD]
[TD]17[/TD]
[TD]1[/TD]
[TD]12.0[/TD]
[TD]9.0
[/TD]
[/TR]
[TR]
[TD]Denzil[/TD]
[TD]18[/TD]
[TD]1[/TD]
[TD]12.0[/TD]
[TD]13.0[/TD]
[/TR]
[TR]
[TD]Gerald[/TD]
[TD]18
[/TD]
[TD]1[/TD]
[TD]15.0[/TD]
[TD]8.0
[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]19[/TD]
[TD]1[/TD]
[TD]16.0[/TD]
[TD]18.0[/TD]
[/TR]
[TR]
[TD]Glen[/TD]
[TD]20[/TD]
[TD]1[/TD]
[TD]17.0[/TD]
[TD]12.0[/TD]
[/TR]
[TR]
[TD]Daryl[/TD]
[TD]21[/TD]
[TD]1[/TD]
[TD]19.0[/TD]
[TD]19.0[/TD]
[/TR]
[TR]
[TD]Tim[/TD]
[TD]22[/TD]
[TD]1[/TD]
[TD]20.0[/TD]
[TD]21.0[/TD]
[/TR]
[TR]
[TD]Dan[/TD]
[TD]23[/TD]
[TD]1[/TD]
[TD]22.0[/TD]
[TD]22.0[/TD]
[/TR]
</tbody>[/TABLE]
The first row of data starts in row 5, column A
The formula I used is:
=RANK.EQ($B5,$B$5:$B$54)+COUNTIFS($B$5:$B$54,$B5,$C$5:$C$54,"<"&$C5)+COUNTIFS($C$5:$C$54,$C5,$D$5:$D$54,"<"&$D5)

Positions 1-10, straight forward as the drivers have unique point totals, positions 11-14, they have the same point totals, however unique Avg Race positions. Where my formula does NOT work is for positions 15-23, all have 1 point and unique ave race positions, except for Daniel & Denzil in positions (which should be 16 & 17), here is where the 2nd tie breaker should be avg qual position, and Daniel with 9.0, and Denzil with 13.0 means Daniel should be ahead in position 16 and Denzil in position 17. Help please !

 

[Marcelo Branco]

Try (assumes data in A1:E24, headers in row 1 - adjust to suit)

B2 copied down
=COUNTIF(C$2:C$24,">"&C2)+COUNTIFS(C$2:C$24,C2,D$2:D$24,"<"&D2)+COUNTIFS(C$2:C$24,C2,D$2:D$24,D2,E$2:E$24,"<"&E2)+1

Hope this helps

M.

 

[rskip]

Cheers Marcelo, works GREAT !!!

 

[Marcelo Branco]

Glad to help

Cheers

M.

 

[shift-del]

Hi

Alternatively you can create a helper column:

=C2-D2%%-E2%%%%

... and rank on this helper column.

=RANK.EQ(H2,$H$2:$H$24)