Explanation
Ordinal numbers represent position or rank in a sequential order. They are normally written using a number + letter suffix: 1st, 2nd, 3rd, etc.
To get an ordinal suffix for a small set of numbers, you can use the CHOOSE function like this:
=CHOOSE(B5,"st","nd","rd","th","th","th","th","th","th","th")
Here CHOOSE simply picks up a number from column B and uses that number as an index to retrieve the right suffix.
A universal formula
With a larger range of numbers it’s not practical to keep adding values to CHOOSE. In that case, you can switch to a more complicated formula that uses the MOD function:
=IF(AND(MOD(ABS(A1),100)>10,MOD(ABS(A1),100)<14),"th",
CHOOSE(MOD(ABS(A1),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))
This formula first uses MOD with AND to “trap” the case of numbers like 11, 12, 13, 111, 112, 113, etc that have a non-standard suffix with is always “th”. All other numbers use the 10 suffix values inside CHOOSE.
The ABS function is used to handle negative numbers as well as positive numbers.
Concatenate suffix to number
You can concatenate (join) the suffix directly using either formula above. For example to add an ordinal suffix to a number 1-10 in A1:
=A1&CHOOSE(A1,"st","nd","rd","th","th","th","th","th","th","th")
But be aware that doing so will change the number into a text value.
Explanation
This formula breaks ties with a simple approach: this first tie in a list “wins” and is assigned the higher rank. The first part of the formula uses the RANK function normally:
=RANK(C5,points)
Rank returns a computed rank, which will include ties when the values being ranked include duplicates. Note that the RANK function by itself will assign the same rank to duplicate values, and skip the next rank value. You can see this in the Rank 1 column, rows 8 and 9 in the worksheet.
The second part of the formula breaks the tie with COUNTIF:
COUNTIF($C$5:C5,C5)-1
Note the range we give COUNTIF is an expanding reference : the first reference is absolute and the second is relative. As long as a value appears just once, this expression cancels itself out – COUNTIF returns 1, from which 1 is subtracted.
However, when a duplicate number is encountered, COUNTIF returns 2, the expression returns 1, and the rank value is increased by 1. Essentially, this “replaces” the rank value that was skipped originally.
The same process repeats as the formula is copied down the column. If another duplicate is encountered, the rank value is increased by 2, and so on.
