Ranking within group with multiple tiebreakers

Hello, everyone. I've spent a lot of time on this and read tons of forum posts on similar issues (and this exact issue) but either no solution I've found works perfectly, or I am failing to implement the solutions correctly. I've gotten really close, but I'm still not quite there. Help would be greatly appreciated.

I have a table of 55 individuals who all have team names, 6-week averages, and 4-week averages. In one column, I want to get their overall rank, and in another column, I want to get their team rank. The ranking needs to be based on the following criteria/tiebreakers:

1) Highest 6-week average (primary criterion)
2) Highest 4-week average (1st tiebreaker)
3) Alphabetically by the individual's name (2nd tiebreaker)


Sample workbook attached, with calculated results next to the expected results as well as highlighted differences between the two.

May be this
Overall rank. In N3 then copy down

Please Login or Register  to view this content.

Overall rank. In Q3 then copy down

Please Login or Register  to view this content.

Thanks, kvsrinivasamurthy. Unfortunately, that didn't quite do the trick.

To be fair though, I may have misled you, because after looking at this again today, I had some things wrong in the expected results column for the Team Rank. I also discovered that I actually had the Overall Rank correct all along and just made an error when checking it. Sorry. My brain was clearly fried after working at this all afternoon yesterday, haha.

I've re-attached my sample workbook, but with my mistakes corrected and the data presented a little better. Do you mind taking another look at the Team Rank?

For over all rank in J3 then copy down

Please Login or Register  to view this content.

For team rank in J3 then copy down

Please Login or Register  to view this content.