I use this formula to display finishing positions in Ordinals in column “I”:-
=IF(LEN(B3)<>5,"",IF(F3=0,0,IF(AND(RANK(F3,Final_Total,1)<14,RANK(F3,Final_Total,1)>10),RANK(F3,Final_Total,1)&“th”,RANK(F3,Final_Total,1)&CHOOSE(MOD(RANK(F3,Final_Total,1),10)+1,“th”,“st”,“nd”,“rd”,“th”,“th”,“th”,“th”,“th”,“th”))))
Note - column B are minutes and secs entered as MM.SS; makes for easier input on a numerical keypad as a simple formular displays the time in HH:MM:SS in another column or other text if competitor Did not start or finish
However I would prefer for the cells in the column “I” to contain just numbers so that I can sort on that column, but use conditional formatting to display the numbers as Ordinals.
How can this be achieved?
Any advice welcomed.