Rank with ordinal suffix

Want to learn how to rank solutions 1st, 2nd, a 3rd on Excel? This post will give you an overview of how to do so using the RANK function in excel.

Unfortunately, there is no inbuilt function or an ordinal number format in Excel. However, there are several methods to show the ordinal number (1st, 2nd, 3rd Etc.) in Excel. The easiest is the formula method.

To use the formula method, you have to first rank your solutions in cardinal format.

To rank in a cardinal format using RANK function, the procedure is as follows:

  • Place the cursor on the empty box you want to rank your first item
  • Enter the formula =RANK(B3,B3:B10). B3 is where our first item begins. B3:B10 are the items we want to use for the ranking.

Figure 1: Ranking in the cardinal format

  • After the ranking of the first item, you should proceed to do this process for all the items.

To change the cardinal ranking to Ordinal ranking format (E.g. 1st, 2nd, 3rd E.tc), you can make use of CHOOSE function. Follow the following steps:

  • Place your cursor on an empty row in the first column.
  • Enter the following formula: =CHOOSE(C3,”st”,”nd”,”rd”,”th”,”th”,”th”,”th”,”th”)

C3 is the address of the cell containing the rank which is to be ranked in the ordinal format. It should be varied according to the particular cell of interest.

The result will display as shown on the screenshot below:

Figure 2: Ordinal ranking by CHOOSE function

  • After the first item is ranked, you will have to repeat this process for all the ranks.

The Way the Formula Works

This CHOOSE function simply considers the rank number and chooses the right suffix from the list of suffixes enclosed in the formula. It is useful when dealing with small sizes of data. Realize that for the formula to work, you have to type the suffixes for the total number of ranks in the worksheet. For example, in Figure 2 above, there is a total of 8 scores that have been ranked. If you look at the formula in D3, you will realize that there is also a total of 8 suffixes beginning with “st”, “nd” and “rd” which represent first, second and third respectively.

However, for large sizes of data, this formula is preferable:

=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 introduces additional functions which are the MOD, AND, and the ABS functions. With the aid of MOD as well as AND functions, the formula addresses the situation of numbers such as 11, 12, 13, 111, 112, 113, and so on, whose suffixes deviate from the normal. For example, ordinarily, the suffix attached to 1 is “st”. However, in 11 and such numbers, the suffix is “th”. Therefore, the formula specifies for them and then lets every other number retain its original suffix just as when using this CHOOSE function. Negative numbers are taken care of by the ABS function.

Although there are other methods, this method has proven to be the easiest and more straightforward compared to the other methods. If you only wish you make use of the ordinal rank, you may delete the cardinal ranking.

=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"))

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar