Sunday , 22 September 2019
Pro tip: Add an ordinal indicator to a value in Excel

Pro tip: Add an ordinal indicator to a value in Excel

20_ordinal_excel.png

Adding an ordinal
indicator – st, nd, rd, and th – uses a suffix to denote the value’s
position within a series. For example, 1 becomes 1st, 2 becomes 2nd, 3 becomes
3rd, and so on. In Excel, you can use a complex formula to create a new string or
you can apply several conditional formatting rules to display the indicator
with the value.

Excel 2003 users must use the formula solution. If you want
to apply the conditional formatting technique, you must have Excel 2007 or
later.


Free
sample Excel worksheets are provided as an educational tool in support of this
pro tip.


The rules

Knowing the rules and their precedence is imperative. Trying
to apply ordinals without knowing the following rules will just make you sad:

  • Values ending in 0 always get th.
  • Values ending in 1 get st unless the value is 11 or
    a value that ends with 11.
  • Values ending in 2 get nd unless the value is 12 or
    a value that ends with 12.
  • Values ending in 3 get rd unless the value is 13 or
    a value that ends with 13.
  • Everything else gets th.

Getting the rules applied in the correct order is the key.
The values 11, 12, and 13 certainly throw a monkey wrench into the works, but
Excel can handle it.


Also
read: 75 essential Excel tips


One formula

In the figure below, I’ve used a formula to combine a value
and its appropriate ordinal indicator:

=value&IF(AND(MOD(ABS(value),100)>10,MOD(ABS(value),100)<14),"th",CHOOSE(MOD(ABS(value),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))

2013251.JPG2013251.JPG

This formula has been in use for a long time. If you try a
shorter version, be sure to check the results for values ending with 11, 12,
and 13 carefully. Most importantly, this formula returns a string, not a value;
you can’t refer to the results of the formula in mathematical equations.

Although long, the formula is simple. The first part of the
formula accommodates values ending with 11, 12, and 13. The second part of the
formula uses CHOOSE() to handle the rest. I suppose you could simplify both
components, but I’ve never tried. This works, and I can’t justify the time it
would take to rethink it. It works with positive and negative integers,
ignoring decimal components.

A conditional format

You can also use a conditional format. This method displays
the indicator with the actual value rather than creating a new string. The
original value remains a numeric value. You only change the way Excel displays
that value.

You’ll need six formulas instead of one; use the formulas
listed in Table A.

Table A

4-9

th

=AND(MOD(ABS(A1),10)>3,MOD(ABS(A1),10)<10)

0

th

=MOD(ABS(A1),10)=0

1

st

=MOD(ABS(A1),10)=1

2

nd

=MOD(ABS(A1),10)=2

3

rd

=MOD(ABS(A1),10)=3

11, 12, 13

th

=AND(MOD(ABS(A1),100)>10,MOD(ABS(A1),100)<14)

You must enter the above rules in their listed order.
There are other routes and other formulas, but this route specifies each rule
in ordinal precedence. If you use other rules, be sure to account for the
application order, which can get messy – it isn’t impossible, but it is
more difficult to follow.

Now, let’s apply the first rule to the values shown below:

  1. Select the data. In this case, that’s A1:H20.
  2. In the Styles group on the Home tab, click Conditional
    Formatting.
  3. Choose New Rule.
  4. In the New Formatting Rule dialog, select the last option
    in the upper pane: Use A Formula To Determine Which Cells To Format.
  5. Enter the “th” rule for the 4-9 rule:

=AND(MOD(ABS(A1),10)>3,MOD(ABS(A1),10)<10)

  1. Click Format.
  2. Click the Number tab.
  3. In the Category list, select Custom.
  4. In the Type field, enter the custom code: 0″th”

You can skip that last step if you like. I’m also using
color to highlight the formatted values. Doing so creates a nice visual trail
to follow, but you probably won’t want to apply color to the values you format
in your own sheets. Click the Fill tab, choose a color, and click OK.

Click OK twice. This first rule adds th to values ending
with the digits 4 through 9.

Repeat this process for the remaining rules, being careful
to add them in the listed order. When you enter the rules for 1, 2, and 3,
you’ll notice that Excel also formats the values ending with 11, 12, and 13,
which is incorrect. Don’t worry because the final rule for 11, 12, and 13 will
override the earlier rules where necessary.

2013252.JPG2013252.JPG

2013253.JPG2013253.JPG

2013254.JPG2013254.JPG

2013255.JPG2013255.JPG

2013256.JPG2013256.JPG

2013257.JPG2013257.JPG

2013258.JPG2013258.JPG

2013259.JPG2013259.JPG

2013260.JPG2013260.JPG

Bottom line

Using conditional formats to apply ordinal indicators can be
problematic. First, you must be careful to consider precedence when entering
the rules. My way isn’t the only way and it isn’t the most efficient, but it is
easy to follow. Second, if you’re working with other conditional formats, you
must continue to consider precedence. You can combine rules and use the Stop If
True property appropriately when combining this set of rules with others. Next
month, I’ll show you how to add ordinal indicators to dates.

2013261.JPG2013261.JPG

Also read:

  • Quick
    Tip: Fill in blank cells in Excel quick and easy
  • Conditional
    formatting tricks: Sum values in Excel by cell color
  • The
    benefits of Excel helper columns

Leave a Reply

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

*