Excel RANK with suffix Function

Sometime you need to add some text at the end of each cell value that is called suffix and in Excel you can add a suffix to an ordinal number in range. Ordinal numbers are ranks in sequential order and suffix letters are add at the end of each ordinal number, like 1st, 2nd, 3rd etc.

You can add suffix letters with ordinal numbers in each cell value one by one by using Excel CHOOSE function, but this is useful for small set of numbers, like if values are from 1 up to 10. So the formula for small set of sequential ordinal values would be;

=CHOOSE(A2,"st","nd","rd","th","th","th","th","th","th","th")

This formula returns a suffix letter in given order for each sequential ordinal number. You can combine suffix letters with each ordinal number by using Ampersand (&) that is a concatenating operator in Excel, such as;

=A2&CHOOSE(A2,"st","nd","rd","th","th","th","th","th","th","th")

But for large set of ordinal numbers CHOOSE function is not helpful to get ordinal suffix as you cannot keep adding suffix letters in CHOOSE function. So for this purpose you need to use following universal formula to return suffix letters for large values that is combination of Excel MOD, ABS, CHOOSE and AND functions within an IF statement, such as

=IF(AND(MOD(ABS(A2),100)>10,MOD(ABS(A2),100)<14),"th",CHOOSE(MOD(ABS(A2),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))

In this formula ABS function handles both negative and positive numbers and MOD with AND function checks the ordinal numbers like 11, 12, 13, 111, 112, 113, etc. in logical_test argument of IF function, that have a non-standard suffix “th”. All other ordinal numbers use the 10 suffix values supplied in CHOOSE function in value_if_false argument.

You can combine the number with suffix by using Ampersand (&) in the formula as below;

=A2&IF(AND(MOD(ABS(A2),100)>10,MOD(ABS(A2),100)<14),"th",CHOOSE(MOD(ABS(A2),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))

Excel Rank with suffix function

As Excel Rank returns relative position of a number within a set of numbers, so you can use CHOOSE function with RANK function to return Excel RANK with suffix function for small set of values.

Suppose you have student scores in an exam and you want to return rank of each student in descending order with suffix letter by using Excel RANK with suffix function. As Excel CHOOSE function returns suffix letter and Excel RANK function returns rank or position of a number in a set, so by using these two functions in a combined formula you can return Excel rank with suffix function as given below;

=RANK(C2,$C$2:$C$8,0)&CHOOSE(RANK(C2,$C$2:$C$8,0),"st","nd","rd","th","th","th","th","th","th","th")

Here in this formula first RANK function returns the rank of a student within range of scores C2:C8 in descending order and CHOOSE function returns the suffix letter based on ranking ordinal number returned by RANK function used within CHOOSE function. The outcomes of these both functions are combined by Ampersand (&) to get Excel RANK with suffix function as shown below.