Tie Breaker when using RANK function - golf scoring template

[ehatz]

I have been having an issue with the Ranking and sorting a team scoring spreadsheet for golf. The top 4 scores count for the team score, which I have solved and then I have it set up to generate a rank for all of the teams. On the score sheet tab the issue that I am having is when a tie occurs between teams. Our state by-laws indicate that if a tie occurs the 5th score will be the tie breaker and if there is a tie through 5 scores then the 6th score will be the tie breaker. Can someone help me with a formula that would help solve this?

Attached it the scoring sheet I have created and been working with. Someone had recommended a RANK with a COUNTIF (example D58, =RANK(E58,$E$58:$E$77,1)+COUNTIF($E$58:E58,E58)-1) but that only ranks them by order they are entered on the sheet if there is a tie. Any suggestions on how to get this to tie break the team score using a 5th score (and if still tied after the 5th to use the 6th score)?

Team Scoring Template.xls

[daffodil11]

I've faced a similar issue when calculating our corporative incentives and came up with a creative solution: inserting tie breakers as decimals.

When a given total exists more than once, I calculate a second time for those scores only but with tie breaker added as a decimal. In this case you're using a number up to three digits long, so we can divide that by 1000. I add one more pass and for ties that still remain I the final tie breaker at 1/1000000 of its original value.

So, imagine if 400 (25, 123), 400 (1, 50), and 400 (25, 120) are tied at 400 points, but have their respective scores in parentheses.

First pass:
400
400
400

Second pass:
400.025
400.001
400.025

Third pass:
400.025123
400.001
400.025120

Now ranking them is considerably easier. Considering bylaws, you may wish to just use Rank(ref, range,1) as the only ranking formula, to preserve ties that remain even with a six game comparison.

[ehatz]

Thanks for the feedback. I used your reference to calculate the team score including the 5th and 6th score but kept the team total to only show whole digits. Then when it ranked them it factored in those two scores.

Issue solved.