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
<colgroup><col width="77"><col width="77"><col width="90"><col width="77"><col width="77"><col width="77"></colgroup> <tbody>
</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!
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
| Mins | Seconds | Milliseconds | Conditions | Time | Rank |
| 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>
</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!
