How to break ranked ties by comparing data in other columns
I'm trying to develop a spreadsheet that will help me to score vertical jump competitions (pole vault or high jump) at track meets. Each competitor is allowed 3 attempts to successfully clear the bar at each height. Some heights are cleared on the first attempt, some on the second and some on the third. If all 3 attempts fail, the competitor is out of the competition.
I've been able to get the results to rank the competitors by their highest bar cleared, but in order to break any ties, the rules look at the competitors who are tied (i.e. cleared the same maximum height) and then proceed to compare how many times they failed to clear the bar at the previous height. If they have the same number of misses (failed attempts) at that previous height, you then look at the next previous height and continue this until you determine that one competitor had fewer misses at a height and that person is then ranked above the other.
In the attached file, Vaulters 003 and 008 both cleared 14' 0" and then were unsuccessful trying to clear 14' 6". When you look at the number of misses at their final successful height (14' 0"), they each have 2 misses. You then go back to 13' 6" and they Vaulter 003 had 1 miss while Vaulter 008 had none. Therefore, Vaulter 008 would be ranked higher (4th place) than Vaulter 003 (5th place).
How can I use the number of misses to break these ties? Is it a formula issue, or would this be done in VBA?
Re: How to break ranked ties by comparing data in other columns
Since formula solution would be complicated, I would suggest a VBA solution.
Alt-F11 to open VBA window, insert/module, paste below code into. Then hit F5 to run.
Save as file with .xlsm format.
PHP Code:
Option Explicit
Sub test()
Dim lr&, i&, j&, k&, c&, rng, arr(), sum, n
lr = Cells(Rows.Count, "A").End(xlUp).Row
rng = Range("C4:K" & lr).Value
ReDim arr(1 To UBound(rng), 1 To 2)
For i = 1 To UBound(rng)
sum = 0: k = 0: c = 0
For j = 1 To 9
If IsNumeric(rng(i, j)) And rng(i, j) <> "" Then
n = (j + 10 ^ -rng(i, j)) * 10 ^ j
sum = sum + n
k = IIf(rng(i, j) = 0, j, j - 1)
c = c + 1
End If
Next
arr(i, 2) = sum
arr(i, 1) = IIf(sum = 0, "DNS", IIf(c = 1, "NH", Cells(3, k + 2).Value))
Next
Range("L4").Resize(UBound(rng), 2).Value = arr
For i = 1 To UBound(arr)
arr(i, 2) = WorksheetFunction.Rank(arr(i, 2), Range("M4:M" & lr))
If arr(i, 1) = "DNS" Then arr(i, 2) = "---"
Next
Range("L4").Resize(UBound(rng), 2).Value = arr
End Sub
Re: How to break ranked ties by comparing data in other columns
bebo021999:
Thank you for this help! It works great for the file I sent. My actual file has space for up to 200 competitors. I tried adding lines for more competitors in my example file and it still worked great until I entered another person whose result was "NH". Then the rankings didn't seem to work. I've attached a revised file that has 21 competitors so show the issue. If a person's result is "NH", I'd like them to automatically be assigned last place (not counting anyone who Did Not Start, "DNS"). Any suggestions on how to change the VBA code would be greatly appreciated.
Re: How to break ranked ties by comparing data in other columns
Try again:
PHP Code:
Option Explicit
Sub test()
Dim lr&, i&, j&, k&, c&, rng, arr(), sum, n
lr = Cells(Rows.Count, "A").End(xlUp).Row
rng = Range("C4:K" & lr).Value
ReDim arr(1 To UBound(rng), 1 To 2)
For i = 1 To UBound(rng)
sum = 0: k = 0: c = 0
For j = 1 To 9
If IsNumeric(rng(i, j)) And rng(i, j) <> "" Then
n = (j + 10 ^ -rng(i, j)) * 10 ^ j
sum = sum + n
k = IIf(rng(i, j) = 0, j, j - 1)
c = c + 1
End If
Next
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks