=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.
=IF(ISNUMBER(I4),RANK(I4,$I$4:$I$34)+COUNTIF($I$4:I4,I4)-1,"") |
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.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.
Thanks anyway.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.
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
@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
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
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.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.