Race results - Problem with rankings

[AJSharpy]

Hi Folks,

Quite often come here to find solutions and the forums are great! However this time i'm totally stumped, so my first post is a plea for help!

I'm running a ski competition and for the races i need to rank competitors based on their time through the course. Which is fine and dandy, the problem comes when a racer posts either a DNS, DNF or DSQ. As per the race rules, those who post a DSQ are still awarded a time, but are then excluded from the ranking. This is what's causing me so much grief!

G H I J K L

1	1	11		01:01:11	2
0	0	0	DNF	00:00:00	#VALUE!
3	3	3	DSQ	03:03:03	#VALUE!
4	4	4		04:04:04	4
5	5	5		05:05:05	5
6	6	6		06:06:06	6
7	7	7		07:07:07	7

<colgroup><col width="77"><col width="77"><col width="90"><col width="77"><col width="77"><col width="77"></colgroup> <tbody>
[TD="width: 77"] Mins [/TD]
[TD="width: 77"] Seconds [/TD]
[TD="width: 90"] Milliseconds [/TD]
[TD="width: 77"] Conditions [/TD]
[TD="width: 77"] Time [/TD]
[TD="width: 77"] Rank [/TD]

</tbody>

Trying to use something like this: =IF($J4="";RANK(K4;$K$4:$K$100;1);IF($J4="*",L4,100))
I need the Rank column to rank those who set a legitimate time, with rank 1 being the quickest time, while giving those who DSQ etc a rank of 100 to list them at the bottom of the results.

Anyone got any ideas? I'm going spare trying to find an answer! Why can't there just be a RANKIF! (Please note, i'm using OpenOffice but the functions etc are basically the same as Excel)

Thanks in advance!

 

[Marcelo Branco]

Try

=IF(ISNUMBER(SEARCH("D",J4)),J4,IF(ISNUMBER(SEARCH("D",P4)),P4,COUNTIFS(J:J,"=",P:P,"=",S:S,"<"&S4)+1))

M.

 

[AJSharpy]

You Sir, are an absolute hero!

Smashed it out the park! Thanks so much for the tips!!

Best,
A