Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Multi-criteria lookup and transpose in Excel
  • Extract all partial matches in Excel
  • Merge tables with VLOOKUP in Excel
  • How to use Excel COLUMN Function
  • Get nth match with INDEX / MATCH in Excel

Data Analysis Examples

  • How to conditionally sum numeric data in an Excel table using SUMIFS
  • How to perform a t-Test in Excel
  • How To Perform and Interpret Regression Analysis in Excel
  • Excel Frequency Function Example
  • How To Load Analysis ToolPak in Excel

Data Validation Examples

  • Excel Data validation don’t exceed total
  • Excel Data validation number multiple 100
  • Excel Data validation specific characters only
  • Excel Data validation must not contain
  • Excel Data validation must begin with

Excel Rank with ordinal suffix Example

by

This tutorials shows how to Rank with ordinal suffix in Excel.

To add an ordinal suffix to a number (i.e. 1st, 2nd, 3rd, etc.) you can use a formula based on the CHOOSE function to assign the suffix.

Formula

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

Explanation

In the example shown, the formula in C5 is:

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

How this formula works

Ordinal numbers represent position or rank in a sequential order. They are normally written using a number + letter suffix:  1st, 2nd, 3rd, etc.

Worked Example:   How to fill cell ranges with random number from fixed set of options in Excel

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.

Worked Example:   Custom weekday abbreviation in Excel

The ABS function is used to handle negative numbers as well as positive numbers.

Worked Example:   Excel Get number at place value Example

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.

Post navigation

Previous Post:

Excel Rank race results Example

Next Post:

Excel Rank without ties Example

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • Get first name from full name — Manipulating NAMES in Excel
  • SEARCH, SEARCHB functions: Description, Usage, Syntax, Examples and Explanation
  • Convert Numbers to Text in Excel
  • FIXED function: Description, Usage, Syntax, Examples and Explanation
  • Normalize text by removing punctuations, extra spaces and more in Excel
  • Convert date to month and year in Excel
  • How to show last updated date stamp in Excel
  • ISOWEEKNUM function: Description, Usage, Syntax, Examples and Explanation
  • Calculate number of hours between two times in Excel
  • YEAR function: Description, Usage, Syntax, Examples and Explanation
  • ISPMT function: Description, Usage, Syntax, Examples and Explanation
  • Calculate cumulative loan interest in Excel
  • Calculate periods for annuity in Excel
  • DOLLARFR function: Description, Usage, Syntax, Examples and Explanation
  • VDB function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2022 xlsoffice . All Right Reserved. | Teal Smiles