Excel 2007 : Competition scores 1st 2nd =3rd etc

[Spike0907]

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

[ConneXionLost]

Hi,

Give this a try:

Please Login or Register  to view this content.

Cheers,

[Spike0907]

Thanks, that's a great help.

Is there any way of limiting the results to the first 6?

[ConneXionLost]

Please Login or Register  to view this content.

[Spike0907]

Thanks m8, your help is much appreciated.

[Spike0907]

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.

[ConneXionLost]

Insert this formula into cell AC9

Please Login or Register  to view this content.

then copy across to AD9 and down to AD14

Cheers,

[Spike0907]

Thanks ConneXionLost, that's worked great.

[Spike0907]

Arrgh - I'm stuck again.

The commentator has asked me if I can arrange for the competitor's number to be available in the Results Box on the right of the Score Sheet, so that he reads, for example: 1st, number 30, Rider's Name, Horse's Name. Second, number 39, Rider's Name, Horse's Name, etc, etc.

I've made the space available in the Results Box, and the formula needn't leave the Score Sheet as the competitor number is there in column A.

Sorry to bother you clever people again, but hopefully this will be the last time.

Many thanks in advance.

[squiggler47]

=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

[Spike0907]

Thanks for your quick reply squiggler47, your formulas are every bit as complicated as I expected. I'm pasting your formulas into cell AC7, and I'm hoping to get the result of 30 for the winner. Unfortunately, I'm only getting a blank cell.

I'm wondering if I made it clear what I am after. I'm hoping to end up with 30 in cell AC7, 39 in AC9, 24 in AC11, and so on, so that the commentator can just read off this Results block "And the winner is number 30, Rider 30 on Horse 30, and in second place is number ........".

Your formulas don't seem to reference column A where the competitor numbers are, nor column Z where the 1st to 6th rankings are. Hoping I'm making myself clear. Thanks.

[daddylonglegs]

Hello Spike,

I think you will have a problem with duplicates. If there are any tied scores then some of the ranks won't exist and you'll get #N/A in your top 6 table.

I revised some of the formulas, see attached

For Z6 copied down I used this version

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

That will give you =4th etc. if you have ties

The "top 6" table can show more than 6 (if for instance there are ties for 6th place) so formulas are copied down to row 20 to display however many entries are in column Z.

I removed the merged cells from that table because they don't work well with some formulas.......

Note: the order in the sheets isn't important as long as the same rider has the same number.......

I changed some results to force ties to demonstrate what will happen - please change some results and see if it does what you want......

[daddylonglegs]

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,"","="))

[squiggler47]

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!

[daddylonglegs]

Nice 1 Squiggler

[Spike0907]

Just a note to say thanks for all your help - it's worked out great.

[Spike0907]

Deleted this post.

[SOLVED] - if a mod would like to change the title