=IF(ISNUMBER(I4),RANK(I4,$I$4:$I$34)+COUNTIF($I$4:I4,I4)-1,"") |

Is there a way to have it randomize first second third, or fifth, sixth, etc of those tied?

Thanks in advance.

You are using an out of date browser. It may not display this or other websites correctly.

You should upgrade or use an alternative browser.

You should upgrade or use an alternative browser.

- Thread starter BobNTN
- Start date

=IF(ISNUMBER(I4),RANK(I4,$I$4:$I$34)+COUNTIF($I$4:I4,I4)-1,"") |

Is there a way to have it randomize first second third, or fifth, sixth, etc of those tied?

Thanks in advance.

- Local time
- Today, 04:29

- Joined
- Feb 28, 2001

- Messages
- 20,062

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......if you Randomize, would you get same result when you re-Calculate?

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.

- Local time
- Today, 04:29

- Joined
- Feb 28, 2001

- Messages
- 20,062

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.

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.

- Local time
- Today, 10:29

- Joined
- Jul 26, 2013

- Messages
- 8,474

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.

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.

@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

**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:

See attached for a way to assign a number, totally at random, which "ranks" each score

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

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

- Assigns a Group Name to each score
- Figures out how many members are in that tie group
- 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.

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

Last edited:

@BobNTN

See attached for a way to assign a number, totally at random, which "ranks" each scorewithin 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 ranks27out of all scores, now let's further rank all27'saccording 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

Hopefully this gives you some ideas.

- Assigns a Group Name to each score
- Figures out how many members are in that tie group
- 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.

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.

- Local time
- Today, 10:29

- Joined
- Jul 26, 2013

- Messages
- 8,474

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.

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.

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.

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.

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:

- Local time
- Today, 10:29

- Joined
- Jul 26, 2013

- Messages
- 8,474

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.

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

- Views
- 152

Total: 1 (members: 0, guests: 1)