Thread: Competition scores 1st 2nd =3rd etc

I have a spreadsheet for a competition, where the final scores are in column T, rows 4 to 62. Some of the cells in column T will be blank where a competitor did not finish, and they are penalty scores, so the lowest number wins.

I am trying to get column U to show 1st, 2nd, 3rd, etc. I also would like the result to show where two competitors have the same score, eg, =3rd, although that is less important.

I am half-way there, but I can't quite finish it.

The formula I have got in T4 (and dragged down the rest) so far is:

=IF(T4="","",RANK(T4,$T$4:$T$62,1))

This formula is not adding st, nd, rd, th, (1st, 2nd, 3rd, 4th), and neither is it putting = on equal scores.

Can someone help put the finishing touches to it, please? Or suggest a different formula if there's a better way of doing it? Maybe a macro would be more suitable?

Many thanks

Hi,

Give this a try:

Code:

=IF(T4="","",RANK(T4,$T$4:$T$62,1) & IF(RANK(T4,$T$4:$T$62,1)>3,"th",CHOOSE(RANK(T4,$T$4:$T$62,1),"st","nd","rd")))

Cheers,

Code:

=IF(T4="","",IF(RANK(T4,$T$4:$T$62,1)>6,"",RANK(T4,$T$4:$T$62,1) & IF(RANK(T4,$T$4:$T$62,1)>3,"th",CHOOSE(RANK(T4,$T$4:$T$62,1),"st","nd","rd"))))

I've nearly got this finished now.

The "Time Sheet" sheet is basically the list of riders' and horses' names. The "Score Sheet" sheet is where the scores will be worked out. I have taken care to ensure that each competitor is on the same line in both worksheets.

I have used a random number generator to fill in all the cells on the Score Sheet, to test that the formulas are working correctly.

As you can see, in Section A competitor No. 30 came 1st, and so on through to 6th.
In Section B, competitor No. 96 came 1st.
In Section C, competitor No. 162 came 1st.

Is it possible to automatically fill-in the boxes on the right of the Score Sheet, with the riders' and horses' names, according to wether they came 1st or 2nd, etc?

So Section A will have 1st - 6th, Section B 1st - 6th, and Section C 1st - 6th.

Many thanks if someone can show me how to do this.

Insert this formula into cell AC9

Code:

=INDEX('Time Sheet'!$B$5:$G$70,MATCH(INDEX($A$5:$A$70,MATCH($AB9,$Z$5:$Z$70,0),1),'Time Sheet'!$B$5:$B$70,0),MATCH('Score Sheet'!AC$8,'Time Sheet'!$B$5:$G$5,0))

then copy across to AD9 and down to AD14

Cheers,

=IF(Y6="","",TEXT(RANK(Y6,$Y$6:$Y$70,1)&MID(" stndrdththth",RANK(Y6,$Y$6:$Y$70,1)*2+1,2),IF(RANK(Y6,$Y$6:$Y$70,1)<7,"@","")))


would also work in a shorter form!

or

=IF(Y6="","",TEXT(MID(" 1st2nd3rd4th5th6th",RANK(Y6,$Y$6:$Y$70,1)*3,3),IF(RANK(Y6,$Y$6:$Y$70,1)<7,"@","")))

even shorter, down to 2 references to the original name

small revision....

The column Z formula works as written but the MIN part is redundant so you can shorten slightly to this version in Z6 copied down

=SUBSTITUTE(IF(Y6="","",TEXT(RANK(Y6,$Y$6:$Y$70,1),"[>6]"""";=0")&LOOKUP(RANK(Y6,$Y$6:$Y$70,1),{1,2,3,4,7;"st","nd","rd","th",""})),"=",IF(COUNTIF(Y$6:Y$70, Y6)=1,"","="))

How about the shorter :-

=IF(Y6="","",RIGHT(MID("=1st=2nd=3rd=4th=5th=6th",RANK(Y6,$Y$6:$Y$70,1)*4-3,4),4-(COUNTIF($Y$6:$Y$70,Y6)=1)))

you can easily change the number of positions just by adding =7th to the end of the list or delete =6th etc for less!