Alternative to using the Choose function

[JonnyStout]

I'm doing a leaderboard and there are over 29 entrants.
I am trying to show what place each person is in and there is also ties.

I was using the Choose function when the entrants were smaller, I didn't know about the 29 limit.
There are 35 entries.

This is the formula I'm using (in this example cell 33):
=IF(OR(A33=A34,A33=A32),"T"&A33,A33)&CHOOSE(A33,"st","nd","rd","th","th","th","th","th","th","th","th","th","th","th","th","th","th","th","th","th","st","nd","rd","th","th","th","th","th","th","th","st","nd","rd","th","th")
Column A has their ranking and it returns it, T if it's tied with the previous and then the correct suffix.
This works up to 29.

I'm wondering if there is a way of adding the suffix without the Choose function.
Or maybe breaking it down to groups of 10 somewhere and calculate it there and then return but I can't figure it out.

 

[awoohaw]

How about:

=IF(OR(A33=A34,A33=A32),"T"&A33,A33)&
SWITCH(A33,1,"st",2,"nd",3,"rd",21,"st",22,"nd",23,"rd","th")

 

[JonnyStout]

How about:

=IF(OR(A33=A34,A33=A32),"T"&A33,A33)&
SWITCH(A33,1,"st",2,"nd",3,"rd",21,"st",22,"nd",23,"rd","th")

I think that's solved it, much appreciated.

 

[awoohaw]

My pleasure. Best wishes.

 

[awoohaw]

Thanks @Joe4.

@JonnyStout: The formula I gave you didn't go to 35th numbers. Here is a more robust formula that should take care of many many more:

=IF(OR(A33=A34,A33=A32),"T"&A33,A33)&
&IF(AND(A33>=11,A33<=13),"th",SWITCH(1*RIGHT(A33,1),1,"st",2,"nd",3,"rd","th"))

 

[JonnyStout]

Thanks, but that's producing a Parse Error
I just expanded your formula to include the 30s and it's fine. I know that if it gets higher it would need refining.

 

[awoohaw]

Thanks, but that's producing a Parse Error
I just expanded your formula to include the 30s and it's fine. I know that if it gets higher it would need refining.

the formula I gave in post 5 should work, i thinks it something with the tie breaker or cell references. I'll get it. I don't like to be 90% correct. I make mistakes, and that is how I learn. Here is a corrected version:

Book1 (version 1).xlsb
	A	B	C	D
32
33	1	1st		1st
34	2	2nd		2nd
35	3	3rd		3rd
36	5	T5th		T5th
37	5	T5th		T5th
38	6	6th		6th
39	7	7th		7th
40	8	8th		8th
41	9	9th		9th
42	10	10th		10th
43	11	11th		11th
44	12	12th		12th
45	13	13th		13th
46	14	14th		14th
47	15	15th		15th
48	16	16th		16th
49	17	17th		17th
50	18	18th		18th
Sheet9

Cell Formulas
Range	Formula
B33:B50	B33	=IF(OR(A33=A34,A33=A32),"T"&A33,A33)& SWITCH(A33,1,"st",2,"nd",3,"rd",21,"st",22,"nd",23,"rd","th")
D33:D50	D33	=IF(OR(A33=A34,A33=A32),"T"&A33,A33)& IF(AND(A33>=11,A33<=13),"th",SWITCH(1*RIGHT(A33,1),1,"st",2,"nd",3,"rd","th"))

 

[JonnyStout]

That's perfect. It works great. Maybe I was missing something the first time

 

[awoohaw]

No, I had an error. I think I had two consecutive ampersands in Post 5 formula. (before and after the line break)