Tie breaking when using Rank (1 Viewer)

BobNTN

Registered User.
Local time
Today, 06:41
Joined
Jan 23, 2008
Messages
308
=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?

Thanks in advance.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:41
Joined
May 7, 2009
Messages
19,175
if you Randomize, would you get same result when you re-Calculate?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:41
Joined
Feb 28, 2001
Messages
27,001
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.
Local time
Today, 06:41
Joined
Jan 23, 2008
Messages
308
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.
Local time
Today, 06:41
Joined
Jan 23, 2008
Messages
308
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
Local time
Today, 05:41
Joined
Feb 28, 2001
Messages
27,001
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.
Local time
Today, 06:41
Joined
Jan 23, 2008
Messages
308
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
Local time
Today, 10:41
Joined
Jul 26, 2013
Messages
10,355
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.
Local time
Today, 06:41
Joined
Jan 23, 2008
Messages
308
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
Local time
Today, 03:41
Joined
Mar 14, 2017
Messages
8,738
@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
    Example.jpg
    216.8 KB · Views: 150
Last edited:

BobNTN

Registered User.
Local time
Today, 06:41
Joined
Jan 23, 2008
Messages
308
@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
Local time
Today, 10:41
Joined
Jul 26, 2013
Messages
10,355
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.
Local time
Today, 06:41
Joined
Jan 23, 2008
Messages
308
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
Local time
Today, 10:41
Joined
Jul 26, 2013
Messages
10,355
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.
Local time
Today, 06:41
Joined
Jan 23, 2008
Messages
308
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.
 

Users who are viewing this thread

Top Bottom