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).

 

[Richard2542]

Oooops…here's an example - Before looks good until we get to 5th Place. After eliminated 6th place, however, we lost "Tied - 1st Place - 2nd Place" and "Tied 3rd Place - 4th Place"....is there a method in the formula to retain the "Before syntax" and eliminate 6th Place (just replace with "Tied - 5th Place"?

Before 1 Tied - 1st Place - 2nd Place 1 Tied - 1st Place - 2nd Place 3 Tied - 3rd Place - 4th Place 3 Tied - 3rd Place - 4th Place 5 Tied - 5th Place - 6th Place 5 Tied - 5th Place - 6th Place 5 Tied - 5th Place - 6th Place After 1 1st Place 1 1st Place 3 3rd Place 3 3rd Place 5 Tied - 5th Place - 5th Place 5 Tied - 5th Place - 5th Place 5 Tied - 5th Place - 5th Place <colgroup><col><col><col span="4"></colgroup><tbody> </tbody> <strike></strike> <colgroup><col><col><col span="3"></colgroup><tbody> </tbody> <colgroup><col><col><col span="3"></colgroup><tbody> </tbody> <colgroup><col><col><col span="3"></colgroup><tbody> </tbody>

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[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([COLOR=#ff0000]AND(COUNTIF($C$2:$C$7,C2)>1,C2<5)[/COLOR]," - "&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]

Perfect...thank you so very much.

 

[Eric W]

You're welcome.