Ranking with Tiebreakers

BradH

New Member
Joined
Jan 25, 2010
Messages
44
Need some help ranking, this is just a small sample. I found a formula on here to rank the teams based on columns D & F, but can't figure out how to use L as a tiebreaker instead of order in the spreadsheet. Obviously this is just a small sample of the sheet there are more columns so the formula won't match up completely. I labeled the columns as I have them in my sheet. I hope I did this right.

Code:
=COUNTIFS(D:D,D2,F:F,">"&F2)+COUNTIFS(D$2:D2,D2,F$2:F2,F2)

Column DColumn FColumn L
ClassPRSFDesired Rank
1A1221
1A1032
1A1023
2A1441
2A1232
2A1233
3A1251
3A1022
3A1023

<tbody>
</tbody>
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
I don't fully understand the requirement.
You said you wanted to use column L as the tiebreaker instead of the order in the sheet. I can see that in the green rows but in the two amber sections you have not used column L to break the ties, you have used something else - perhaps the order in the sheet that you said you did not want to use.. :confused:


Excel Workbook
DFLM
1ClassPRSFDesired Rank
21A1221
31A1032
41A1023
52A1441
62A1232
72A1233
83A1251
93A1022
103A1023
Rank in Groups
 
Last edited:
Upvote 0
Thanks Peter for your reply,

You are right, it appears that I am using order in the sheet as a tiebreaker still, and technically I am. But as a third tiebreaker, I want L to be the 2nd tiebreaker. This is for football, and once we get 3-4 weeks into the season, these numbers will separate and the need for the third tiebreaker will almost always go away.
 
Upvote 0
In that case, try this.

Excel Workbook
DFLM
1ClassPRSFRank
21A1221
31A1032
41A1023
52A1441
62A1232
72A1233
83A1251
93A1022
103A1023
Rank in Groups
 
Upvote 0
Thanks for your help. I had to have this done yesterday so I spent quite a bit of time searching more posts on here and came across one that worked. I created a helper column and added column L (times .0001) to column F, and then ran my original formula on the new column instead of F. It appears to work like a charm. In one instance I had 15 ties on column F, and it sorted them all properly.

I will give yours a try when I get a chance and see how it does.
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

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