How to break ranked ties by comparing data in other columns

[hennie-bogan]

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?