DEV Community

Andrews
Andrews

Posted on • Originally published at geekexcel.com on

Excel Formulas to Set the Rank with Ordinal Suffix ~ {Easy Method}

Using the RANK function you can easily compare numbers to other numbers in the same list. In this chapter, we will discuss some cute formulas to add an ordinal suffix to rank numbers (such as 1st, 2nd, 3rd, etc.) in Excel. Let’s jump into this article!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

Rank with ordinal suffix
Rank with an ordinal suffix

General Formula:

  • For adding the ordinal suffix to a number you can use the below formula.

=CHOOSE(number,”st”,”nd”,”rd”,”th”,”th”,”th”,”th”,”th”,”th”,”th”)

Syntax Explanations:

  • CHOOSE – In Excel, theCHOOSE function returns a value from the list of value arguments using a given position or index.
  • Number – It represents the input ranges.
  • Comma symbol (,) – It is a separator that helps to separate a list of values.
  • Parenthesis () – The main purpose of this symbol is to group the elements.

Practical Example:

Refer to the below example image.

  • First, we will enter the input values in Column B.

Input Ranges
Input Ranges

  • Now enter the formula in the selected cell.

Enter the formula
Enter the formula

  • Finally, we will get the result as per the below image.

Result
Result

Closure:

From this short article, you can get some clarification on how to add an ordinal suffix to rank numbers (such as 1st, 2nd, 3rd, etc.) in Excel. If you have any doubt regarding this article or have any other questions related to Excel, let me know in the comments section below. Thank You!! Click here to know more about Geek Excel *and Excel Formulas *!!

Read Also:

Top comments (0)