Mind Boggling High Jump Rankings

[shudra]

Hey Everyone

Me and my friend are often in charge of scoring the high jump at athletics competitions. At the mo, we are trying to make a spreadsheet that automatically works out the competitor rankings for us as it currently takes us a long time doing it by hand! Can anyone maybe have a read and help?? I have had a quick play with different formulas but cannot think of how it can be done.

OK. Firstly, High Jump Notation:
X = Fail (competitor knock off the bar)
O = Clearance (competitor gets over over the bar)
- = Pass (no attempt is made at the current height)

And the ranking rules are as follows:

Rule 1) Competitors are ranked in order of their highest height cleared e.g. 1.56 beats 1.55

Rule 2) When competitors tie at their highest height cleared, competitors are ranked in order of fewest failed attempts at that height only e.g. XO at 1.51 beats XXO at 1.51

Rule 3) When competitors tie at their highest height cleared and also tie for the number of failed attempts at their highest height cleared (e.g. more than 1 competitor gets XXO at 1.39), the competitors are ranked in order of fewest failed attempts at all heights throughout the entire competition but excluding any failed attempts beyond their best cleared height.

N.b. A competitor is out of the competition when they make 3 consecutive failed attempts (including 3 consecutive failed attempts split over multiple heights and/or 3 consecutive failed attempts split up by passes at heights)

Any help or guidance would be greatly appreciated. Ideally, I would love it to be able to automatically calculate best scores and rankings like in this example:

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]1.51[/TD]
[TD="align: center"]1.52[/TD]
[TD="align: center"]1.53[/TD]
[TD="align: center"]1.54[/TD]
[TD="align: center"]1.55[/TD]
[TD="align: center"]1.56[/TD]
[TD="align: center"]1.57[/TD]
[TD="align: center"]1.58[/TD]
[TD="align: center"]1.59[/TD]
[TD="align: center"]1.60[/TD]
[TD="align: center"]Best[/TD]
[TD="align: center"]Ranking[/TD]
[/TR]
[TR]
[TD="align: center"]Alice[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]XXO[/TD]
[TD="align: center"]XXX[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1.57[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]Brooke[/TD]
[TD="align: center"]XO[/TD]
[TD="align: center"]XXO[/TD]
[TD="align: center"]XO[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]XXO[/TD]
[TD="align: center"]XXO[/TD]
[TD="align: center"]XO[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1.57[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]Caitlin[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]XXO[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]X-[/TD]
[TD="align: center"]XO[/TD]
[TD="align: center"]XO[/TD]
[TD="align: center"]XXX[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1.56[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]Danielle[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]XXO[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]XO[/TD]
[TD="align: center"]XO[/TD]
[TD="align: center"]XX-[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1.56[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]Emily[/TD]
[TD="align: center"]X-[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]XX-[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]XX-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]1.56[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]Freya[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]XX[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]1.55[/TD]
[TD="align: center"]6[/TD]
[/TR]
</tbody>[/TABLE]

Brooke wins because she has fewer attempts than Alice at their best height of 1.57
Emily beats both Caitlin and Danielle because she has fewer attempts at their best height of 1.56
Danielle beats Caitlin because although they both have 1 failed attempt at their best height of 1.56, Danielle only makes 4 failed attempts throughout the entire competition up to her best height whereas Caitlin makes 5.
Freya comes last because she has the lowest best height cleared of 1.55

Can anyone help?
Thank You!

 

[Marcol]

You have also asked for help with this in Excel Forum.

Because I can post a workbook in Excel Forum, see there for a possible solution.
http://www.excelforum.com/excel-gen...high-jump-rankings.html?p=2848790#post2848790