Calculating positions in a race

[[email protected]]

I have the following results for 100meters race and 200 metres race:
200meters 6.5mins
200meters 6.2mins
100meters 3.8mins
100meters 3.4mins
100meters 3.9mins.
How do I calculate 1st, 2nd, etc positions for the two categories?

[Andrew-R]

Well, I've the nagging suspicion that there must be an easier way, but with the values you've given in cells A1:B5 the following formula in C1 (and dragged down) works:

=MATCH(VALUE(SUBSTITUTE(B1,"mins","")),INDEX(SMALL(IF($A$1:$A$5=A1,VALUE(LEFT($B$1:$B$5,FIND("mins",$B$1:$B$5)-1))),ROW(INDIRECT("1:" & COUNTA($A$1:$A$100)))),0))

This is an array formula and must be confirmed with Ctrl-Shift-Enter, not just Enter.

[Andrew-R]

Duplicate post, sorry.

[[email protected]]

Thanks Andrew- R. I got a shorter formular. If the distances are in A1:A5, and durations are in B1:B5, to get the first for 200meters meters, type 200 meters in C1. Then use this formular: =max(if(A1:A5=C1,B1:B5)) Control+Shift+Enter.
Once more, thanks for being there.