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

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!

 

[mole999]

You could make your first lookup up check for D i.e IF(LEFT(CELL,1)="D" something like that and exclude them ?

 

[Marcelo Branco]

Maybe this...

	G​	H​	I​	J​	K​	L​
1​	Mins​	Seconds​	Milliseconds​	Conditions​	Time​	Rank​
2​	1​	1​	11​		01:01:11​	1​
3​	0​	0​	0​	DNF​	00:00:00​	100​
4​	3​	3​	3​	DSQ​	03:03:03​	100​
5​	4​	4​	4​		04:04:04​	2​
6​	5​	5​	5​		05:05:05​	3​
7​	6​	6​	6​		06:06:06​	4​
8​	7​	7​	7​		07:07:07​	5​

Formula in L2 copied down
=IF(J2<>"",100,COUNTIFS(J:J,"=",K:K,"<"&K2)+1)

Hope this helps

M.

 

[AJSharpy]

Maybe this...

	G​	H​	I​	J​	K​	L​
1​	Mins​	Seconds​	Milliseconds​	Conditions​	Time​	Rank​
2​	1​	1​	11​		01:01:11​	1​
3​	0​	0​	0​	DNF​	00:00:00​	100​
4​	3​	3​	3​	DSQ​	03:03:03​	100​
5​	4​	4​	4​		04:04:04​	2​
6​	5​	5​	5​		05:05:05​	3​
7​	6​	6​	6​		06:06:06​	4​
8​	7​	7​	7​		07:07:07​	5​

<tbody>
</tbody>

Formula in L2 copied down
=IF(J2<>"",100,COUNTIFS(J:J,"=",K:K,"<"&K2)+1)

Hope this helps

M.

This is an absolute beauty! Works a dream in Excel so just need to figure out the conversion to OpenOffice now. Cheers for the help!

 

[Marcelo Branco]

You are welcome. Glad to help

M.

 

[AJSharpy]

<tbody>
</tbody>

You are welcome. Glad to help

M.

Ok, any chance you can help take it up a notch?

Racers all have 2 runs, with their total time determining their final rank. However a DNS/DNF etc in either run 1 or run 2 means they don't receive a final rank (Rank 100 in the above example). My crude attempts so far for the 'Final Rank' are: =IF(J4<>"",100,IF(P4<>"",100,COUNTIFS(P:P,"=",J:J,"=",S$4:S$100,"<"&S4)+1)) which returns "100" if there is a DNF etc present in either column P or J, however if the racer sets a legitimate time in both the formula doesn't function (returns #VALUE!).

Run 1	Run 2
Mins	Seconds	Milliseconds	Conditions	Time	Rank	Mins	Seconds	Milliseconds	Conditions	Time	Rank	Total	Final Rank
1	1	1	dnf	01:01:01	100	1	1	1		01:01:01	1	02:02:02	100
2	2	2		02:02:02	1	2	2	2		02:02:02	1	04:04:04	#VALUE!
3	3	3		03:03:03	1	3	3	3	dsq	03:03:03	100	06:06:06	100
4	4	4		04:04:04	1	4	4	4		04:04:04	1	08:08:08	#VALUE!
5	5	5		05:05:05	1	5	5	5		05:05:05	1	10:10:10	#VALUE!
6	6	6		06:06:06	1	6	6	6		06:06:06	1	12:12:12	#VALUE!
7	7	7		07:07:07	1	7	7	7		07:07:07	1	14:14:14	Err:502
8	8	8		08:08:08	1	8	8	8		08:08:08	1	16:16:16	Err:502

<colgroup><col width="86"><col width="86"><col width="86"><col width="86"><col width="86"><col width="86"><col width="86"><col width="86"><col width="86"><col width="86"><col width="86"><col width="86"><col width="86"><col width="86"></colgroup> <tbody>
</tbody>

 

[Marcelo Branco]

Maybe...

=IF(OR(J4<>"",P4<>""),100,COUNTIFS(J:J,"=",P:P,"=",S:S,"<"&S4)+1)

M.

 

[AJSharpy]

Yep, you're good! Awesome solution!

One last one i promise! I've been getting a bit more creative and managed to change the '100' ranking for the correct DSQ/DNF. Any pointers for how to ammend that last formula to reflect this? So Final Rank will pull through a DSQetc from either Run Ranking?

Run 1	Run 2
Mins	Seconds	Milliseconds	Conditions	Time	Rank	Mins	Seconds	Milliseconds	Conditions	Time	Rank	Total	Final Rank
1	1	1		01:01:01	1	1	1	1	dnf	01:01:01	dnf	02:02:02	100
2	2	2		02:02:02	2	2	2	2		02:02:02	1	04:04:04	1
3	3	3		03:03:03	3	3	3	3		03:03:03	2	06:06:06	2
4	4	4	dsq	04:04:04	dsq	4	4	4		04:04:04	3	08:08:08	100

<colgroup><col span="2"><col span="2"><col span="4"><col span="2"><col span="2"><col><col></colgroup><tbody>
</tbody>

My equations for giving the DSQ/DNF/DNS in the ranking columns is: =IF(ISNUMBER(SEARCH("D",P4)),P4,COUNTIFS(P:P,"=",Q:Q,"<"&Q4)+1) which works a treat (ammended your original suggestion, thanks for the tips!) but no idea how to combine that to also search for the 'D' result from J4 in Run1, giving the 'Final Rank' column either a ranking number or a DSQ/DNF/DNS.

Thanks in advance again!
A

 

[Marcelo Branco]

Not sure i understand what you need...

This?
=IF(ISNUMBER(SEARCH("D",J4&P4)),......

M.

 

[AJSharpy]

That's almost the one! Sorry i can't explain it better but you're on the right track!

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

This returns the right result if the DSQ is in J4, but doesn't take into account any 'D' results in P4.