# Tie breaking when using Rank (1 Viewer)

#### BobNTN

##### Registered User.
 =IF(ISNUMBER(I4),RANK(I4,\$I\$4:\$I\$34)+COUNTIF(\$I\$4:I4,I4)-1,"")
This will break ties but it assigns 1, 2, 3, 4 etc starting at the first in the list then the next then the next and so on.
Is there a way to have it randomize first second third, or fifth, sixth, etc of those tied?

#### arnelgp

if you Randomize, would you get same result when you re-Calculate?

#### The_Doc_Man

##### Immoderate Moderator
Staff member
My question is one of purpose, not ability. If those items ARE tied, why do you care the order? That is not an idle question; I'm looking for hidden secondary tie-breaker criteria.

#### BobNTN

##### Registered User.
if you Randomize, would you get same result when you re-Calculate?
I would think it would do the same regardless. This is for a large group in a golf tournament. Either way, the only PURE way of a tie breaker is to go to the hardest handicap holes and mitigate on each score starting at the hardest hole. But that would be so difficult......

#### BobNTN

##### Registered User.
My question is one of purpose, not ability. If those items ARE tied, why do you care the order? That is not an idle question; I'm looking for hidden secondary tie-breaker criteria.
In theory, if my list of players is in alphabetical order and I was first in the list I would win every tie that I had. As I stated in the other reply, the only PURE way to tie break in golf is to mitigate based on the score of those tied by hardest handicap hole in order. But don't even want to go there.

#### The_Doc_Man

##### Immoderate Moderator
Staff member
OK, then the problem is your perception. In a golf tournament where there is a prize, be it cash or points, they just split that prize evenly among all those who are tied. So if you tied 2nd/3rd, add the prizes for each one together and split them evenly. Being first in the list of tied participants doesn't mean you won. It means the stupid computer put you first because it felt like it - but the SCORE or the tie rankings should make it clear as to who ACTUALLY won. You perceive being "first in the list" as a win when in fact it is not.

I'm not actually trying to give you a hard time about this. You point out that you don't want to take on the "real" tiebreaker rule. But there are implications of choosing to not break the tie. Like... having unbroken ties. It is a perception and a decision that you are making.

#### BobNTN

##### Registered User.
OK, then the problem is your perception. In a golf tournament where there is a prize, be it cash or points, they just split that prize evenly among all those who are tied. So if you tied 2nd/3rd, add the prizes for each one together and split them evenly. Being first in the list of tied participants doesn't mean you won. It means the stupid computer put you first because it felt like it - but the SCORE or the tie rankings should make it clear as to who ACTUALLY won. You perceive being "first in the list" as a win when in fact it is not.

I'm not actually trying to give you a hard time about this. You point out that you don't want to take on the "real" tiebreaker rule. But there are implications of choosing to not break the tie. Like... having unbroken ties. It is a perception and a decision that you are making.
Thanks anyway.

#### Minty

##### AWF VIP
In golf, the general rule for a tie is based on countback.

18 hole overall score ties, then compare the total Net Scores for the last 9 holes on the scorecard.
If still a tie then the last 6 Holes
If still a tie then the last 3 Holes
if still a tie then the lowest score on the last hole.

If it's still a tie you are really unlucky.

#### BobNTN

##### Registered User.
Thanks for all the lessons in breaking ties in golf. I am well aware of those methods. Although they have little to nothing to do with my original question, I appreciate your time. Apparently, it appears, the answer to my question is "no".
Furthermore, just so it is clear, time constraints at the end of this annual tournament restrict going back to a lot of scorecards to break a lot of ties.

#### Isaac

##### Lifelong Learner
@BobNTN

See attached for a way to assign a number, totally at random, which "ranks" each score within its tie group
Perhaps you could figure out a way to utilize this 'random tiebreaker number' in a formula - perhaps a formula similar to what is in E4 - to decide "okay, this score ranks 27 out of all scores, now let's further rank all 27's according to their (randomly generated) "inner" tie breaking number rank.

Might make more sense to look at it...attached

Another flaw in my example, which you'd have to put some probably minor thought into solving, is that in my example, all scores are in Tied Groups, whereas in real life, only some of your scores tie with others, not all.

This macro basically
1. Assigns a Group Name to each score
2. Figures out how many members are in that tie group
3. Assigns a ranking number, which you can tell your organization is totally randomized, for that score within its tie group - so if there are 5 members of a tie group, each member will be randomly assigned a number between 1 and 5, and distinct within their group.
Hopefully this gives you some ideas.

Edit, well, this is annoying, I can't upload an xlsm or xlsb file here. So here is a screenshot of what it does, where exactly to put your data, and the code below:

Code:
``````Option Explicit

Sub AssignRandomTiebreakingRank(rngAllScores As Range)
Dim lngTotalNumberOfSameScores As Long, rngOuter As Range, rngInner As Range, lngRandomTiebreakerNumber As Long
Dim strGroupName As String, lngUpperBound As Long

'figure out how many scores are the same as this score:
For Each rngOuter In rngAllScores
rngOuter.Offset(0, 1).Value = "=COUNTIF(" & rngAllScores.Address & "," & rngOuter.Address & ")"
rngOuter.Offset(0, 2).Value = "Group " & rngOuter.Value
Next rngOuter

'assign random tiebreaker number, to be distinct within the range of 'same scores' number:
For Each rngOuter In rngAllScores
lngTotalNumberOfSameScores = rngOuter.Offset(0, 1).Value
strGroupName = rngOuter.Offset(0, 2).Value
lngRandomTiebreakerNumber = 0
lngUpperBound = rngOuter.Offset(0, 1).Value
Do Until (lngRandomTiebreakerNumber <> 0) And (Application.WorksheetFunction.CountIfs(Range("L4:L34"), lngRandomTiebreakerNumber, Range("k4:k34"), strGroupName) = 0)
lngRandomTiebreakerNumber = Application.WorksheetFunction.RandBetween(1, lngUpperBound)
Loop
rngOuter.Offset(0, 3).Value = lngRandomTiebreakerNumber
Next rngOuter

End Sub
Sub Test()
AssignRandomTiebreakingRank Range("i4:i34")
End Sub``````

#### Attachments

• Example.jpg
216.8 KB · Views: 9
Last edited:

#### BobNTN

##### Registered User.
@BobNTN

See attached for a way to assign a number, totally at random, which "ranks" each score within its tie group
Perhaps you could figure out a way to utilize this 'random tiebreaker number' in a formula - perhaps a formula similar to what is in E4 - to decide "okay, this score ranks 27 out of all scores, now let's further rank all 27's according to their (randomly generated) "inner" tie breaking number rank.

Might make more sense to look at it...attached

Another flaw in my example, which you'd have to put some probably minor thought into solving, is that in my example, all scores are in Tied Groups, whereas in real life, only some of your scores tie with others, not all.

This macro basically
1. Assigns a Group Name to each score
2. Figures out how many members are in that tie group
3. Assigns a ranking number, which you can tell your organization is totally randomized, for that score within its tie group - so if there are 5 members of a tie group, each member will be randomly assigned a number between 1 and 5, and distinct within their group.
Hopefully this gives you some ideas.

Edit, well, this is annoying, I can't upload an xlsm or xlsb file here. So here is a screenshot of what it does, where exactly to put your data, and the code below:

Code:
``````Option Explicit

Sub AssignRandomTiebreakingRank(rngAllScores As Range)
Dim lngTotalNumberOfSameScores As Long, rngOuter As Range, rngInner As Range, lngRandomTiebreakerNumber As Long
Dim strGroupName As String, lngUpperBound As Long

'figure out how many scores are the same as this score:
For Each rngOuter In rngAllScores
rngOuter.Offset(0, 1).Value = "=COUNTIF(" & rngAllScores.Address & "," & rngOuter.Address & ")"
rngOuter.Offset(0, 2).Value = "Group " & rngOuter.Value
Next rngOuter

'assign random tiebreaker number, to be distinct within the range of 'same scores' number:
For Each rngOuter In rngAllScores
lngTotalNumberOfSameScores = rngOuter.Offset(0, 1).Value
strGroupName = rngOuter.Offset(0, 2).Value
lngRandomTiebreakerNumber = 0
lngUpperBound = rngOuter.Offset(0, 1).Value
Do Until (lngRandomTiebreakerNumber <> 0) And (Application.WorksheetFunction.CountIfs(Range("L4:L34"), lngRandomTiebreakerNumber, Range("k4:k34"), strGroupName) = 0)
lngRandomTiebreakerNumber = Application.WorksheetFunction.RandBetween(1, lngUpperBound)
Loop
rngOuter.Offset(0, 3).Value = lngRandomTiebreakerNumber
Next rngOuter

End Sub
Sub Test()
AssignRandomTiebreakingRank Range("i4:i34")
End Sub``````

Thank you so much Isaac. I can work with that.

#### Minty

##### AWF VIP
Thanks for all the lessons in breaking ties in golf. I am well aware of those methods. Although they have little to nothing to do with my original question, I appreciate your time. Apparently, it appears, the answer to my question is "no".
Furthermore, just so it is clear, time constraints at the end of this annual tournament restrict going back to a lot of scorecards to break a lot of ties.
I'm on our golf clubs competition committee, we enter front and back 9 total scores for all the entrants, it takes about 2 minutes to work out the overall results and on the odd occasions the front / back split doesn't sort things out it takes another 3-4 minutes at worst to sort out the top ten other count backs. Beyond the top ten doesn't normally matter.

When we enter the cards we put to one side the top 10-15 cards out of 100+ as we are entering the scores. If you want them sorted all the way down for every entry base it on entry time. First card in above the next.

Obviously, this only works if you are able to identify the front and back scores.

#### BobNTN

##### Registered User.
Thanks Minty. You gave me a couple more ideas to consider.
This is an annual 2 man team 3 day event strictly based on points system we have been having for around 28 years.
We usually have 50 to 60 players, this year 66 and next year looks like 70.
We adjust each player's points after each round for the next round.
We paid 8 teams in the team event plus 6 in the individual event. We also have SCATS (Skins) along with closest to the pin each day.
Plus, we have a random drawn 'foursome' (4 places) each day which can also generate ties. That's 48 players!
The least driving anyone has to get back home after Sunday round is 3 to 3.5 hours and about 12 players drive 8 to 9 hours.
Needless to say, the Sunday wrap up time is crucial to get everyone on the road home.
So, the basis of what I was trying to do in breaking ties is purely to reduce time without being overly unfair. I certainly don't want to have to put all 18 hole scores in for every player every day.
As far as holding the top ten or so cards, we have no idea since it is a 3 day event and those top ten for Sunday mean little to the overall standings. So I like your idea of the front or back nine total for tie breaker since that total would be on the cards.
Anyway, I have refined this workbook every year with lots of help from this forum which I am very grateful. I would love to do it in Access but Excel is probably the easiest.

Last edited:

#### Minty

##### AWF VIP
To be fair - all our stuff for those type of Comps is done in Excel for simplicity.
It sounds like you have it covered.

I keep thinking of doing an Access golf competition DB but there are a lot of different types, and it's difficult to adapt thing to all of them.

#### BobNTN

##### Registered User.
Yeah, I guess if someone looked at mine they would be confused but over time and changes and refinements I have made (for my personal reasons) it works for me. I thought about starting it all over to simplify but not sure if it is worth all the trouble.
We play 2 courses over 3 days and it is a great tournament. Had near \$8000 in prizes this year. We stay in nice condos right on the fairway. I have not found any other place even close to the price we pay for what we get.

Replies
7
Views
166
Replies
16
Views
209
Replies
6
Views
152
Replies
2
Views
232
Replies
14
Views
489