Excel - non vba code - Associate a Text Phrase to a Group of Ranked Numbers

[Richard2542]

I have ranked a list of players by score (low to high using the Rank function) and wish to designate (using text) the present standing (1 to 5 places) of the player. If 2 players are ranked 1, the next lowest player(s) would be ranked 3, etc.
I would like to designate, via text in a Notes column adjacent to the player's name, the phrase 1st Place or Tied 1st and 2nd Place, etc. I would like to accomplish this by formula (non vba code).

 

[Fluff]

How about
C2&LOOKUP(C2,{1,2,3,4},{"st","nd","rd","th"})&" Place"&IF(COUNTIF($C$2:$C$6,C2)>1," Tied","")

 

[Richard2542]

Thanks...formula worked really nice - I would like the ties to reflect the possible positions, e.g., if the 1st 2 players are tied for 1st, then the text should read "Tied - 1st and 2nd Place".

 

[Fluff]

Swap the formula round like this
=IF(COUNTIF($C$2:$C$8,C2)>1,"Tied ","")&C2&LOOKUP(C2,{1,2,3,4},{"st","nd","rd","th"})&" Place"

If 2 people are tied for 1st place they are not 1st & 2nd, just 1st.
also what would happen if 4 people tied?

 

[Eric W]

Maybe this variation of Fluff's formula?

	A	B	C	D
Name	Score	Rank	Notes
a	Tied - 1st Place - 2nd Place
b	Tied - 3rd Place - 5th Place
c	Tied - 3rd Place - 5th Place
d	6th Place
e	Tied - 3rd Place - 5th Place
f	Tied - 1st Place - 2nd Place

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]68[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]70[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]70[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]74[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]70[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]68[/TD]
[TD="align: right"]1[/TD]

</tbody>

​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]D2[/TH]
[TD="align: left"]=IF(COUNTIF($C$2:$C$7,C2)>1,"Tied - ","")&C2&LOOKUP(C2,{1,2,3,4},{"st","nd","rd","th"})&" Place"&IF(COUNTIF($C$2:$C$7,C2)>1," - "&C2+COUNTIF($C$2:$C$7,C2)-1&LOOKUP(C2+COUNTIF($C$2:$C$7,C2)-1,{1,2,3,4},{"st","nd","rd","th"})&" Place","")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Richard2542]

Fluff...the two or more players tied for 1st would be shown in the notes section (each player - adjacent to their name): Tied - 1st and 2nd Place.
If there were a total of 10 players and each player had the same score (highly unusual) then each player would have the designation Tied 1st, 2nd, 3rd, 4th and 5th Place...a bit more complicated.

 

[Richard2542]

Eric...this looks like it will work - wow, nice work.

 

[Richard2542]

Eric and Fluff - here's what I just tested: Player 1 and 2 are ranked 1 (formula displays "Tied - 1st Place - 2nd Place", Player 3 and 4 are ranked 3 (formula displays "Tied 3rd Place - 4th Place", Player 5 and 6 are ranked 5 (formula displays "Tied 5th - 6th Place".
The output is perfect till we get to Player 5 & 6 - since we are only awarding 5 places I need to eliminate 6th Place.

 

[Eric W]

Try:

=IF(C2>5,"",IF(COUNTIF($C$2:$C$7,C2)>1,"Tied - ","")&C2&LOOKUP(C2,{1,2,3,4},{"st","nd","rd","th"})&" Place"&IF(COUNTIF($C$2:$C$7,C2)>1," - "&MIN(C2+COUNTIF($C$2:$C$7,C2)-1,5)&LOOKUP(MIN(C2+COUNTIF($C$2:$C$7,C2)-1,5),{1,2,3,4},{"st","nd","rd","th"})&" Place",""))

 

[Richard2542]

Eric...this works, absolutely brilliant - is there an enhancement that would eliminate "[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Tied - 5th Place - 5th Place" and just return "Tied - 5th Place"?
I can't tell you how much I appreciate your solution(s).[/FONT]