Ranking with Tiebreakers

[BradH]

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.

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

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Column D[/TD]
[TD]Column F[/TD]
[TD]Column L[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class[/TD]
[TD]PR[/TD]
[TD]SF[/TD]
[TD]Desired Rank[/TD]
[/TR]
[TR]
[TD]1A[/TD]
[TD]12[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1A[/TD]
[TD]10[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1A[/TD]
[TD]10[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2A[/TD]
[TD]14[/TD]
[TD]4[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2A[/TD]
[TD]12[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2A[/TD]
[TD]12[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3A[/TD]
[TD]12[/TD]
[TD]5[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3A[/TD]
[TD]10[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3A[/TD]
[TD]10[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

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

Excel Workbook
	D	F	L	M
1	Class	PR	SF	Desired Rank
2	1A	12	2	1
3	1A	10	3	2
4	1A	10	2	3
5	2A	14	4	1
6	2A	12	3	2
7	2A	12	3	3
8	3A	12	5	1
9	3A	10	2	2
10	3A	10	2	3
Rank in Groups

 

[BradH]

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.

 

[Peter_SSs]

In that case, try this.

Excel Workbook
	D	F	L	M
1	Class	PR	SF	Rank
2	1A	12	2	1
3	1A	10	3	2
4	1A	10	2	3
5	2A	14	4	1
6	2A	12	3	2
7	2A	12	3	3
8	3A	12	5	1
9	3A	10	2	2
10	3A	10	2	3
Rank in Groups

 

[BradH]

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.