Rank using 2 tie breaker columns

rskip

New Member
Joined
Nov 26, 2009
Messages
38
Office Version
  1. 365
Platform
  1. Windows
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
Driver Name
Point Ranking

<tbody>
</tbody>
Points Total

<tbody>
</tbody>
Avg Race Position

<tbody>
</tbody>
Avg Qual Postion

<tbody>
</tbody>
Vance
1501.02.0
Ron2283.52.0
Darren3234.59.5
Scott4215.015.0
Steve5205.59.0
Robert6146.59.0
Jesse7105.01.0
Randy8912.04.5
William988.010.0
Reid1067.05.0
Greg11210.54.5
Troy12211.514.5
Bernie13213.515.0
Norman14217.510.5
CR15111.010.0
Daniel17112.09.0
Denzil18112.013.0
Gerald18
115.08.0
Jim19116.018.0
Glen20117.012.0
Daryl21119.019.0
Tim22120.021.0
Dan23122.022.0

<tbody>
</tbody>
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 !
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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.
 
Last edited:
Upvote 0
Hi

Alternatively you can create a helper column:
Code:
=C2-D2%%-E2%%%%

... and rank on this helper column.
Code:
=RANK.EQ(H2,$H$2:$H$24)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top