# Rank problem

#### cpg84

##### Active Member
I have a column that ranks the list next to it. What I want to do is, instead of having 1,1,2,3,4,4 etc, I want it to say 1st,1st,2nd,3rd,4th,4th and so on. Just want the suffix on the end of the rank.

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### tactps

##### Well-known Member
You could try a formula, however this will change the result to text:
=IF(AND(A1>10,A1<20),A1&"th",IF(RIGHT(A1,1)="1",A1&"st",IF(RIGHT(A1,1)="2",A1&"nd",IF(RIGHT(A1,1)="3",A1&"rd",A1&"th"))))

This assumes that your rank formula is in cell A1. You could also replace A1 in the formula with the rank formula you use. e.g.:
=IF(AND(RANK(A1,A:A,1)>10,RANK(A1,A:A,1)<20),RANK(A1,A:A,1)&"th",IF(RIGHT(RANK(A1,A:A,1),1)="1",RANK(A1,A:A,1)&"st",IF(RIGHT(RANK(A1,A:A,1),1)="2",RANK(A1,A:A,1)&"nd",IF(RIGHT(RANK(A1,A:A,1),1)="3",RANK(A1,A:A,1)&"rd",RANK(A1,A:A,1)&"th"))))

#### Yogi Anand

##### MrExcel MVP
Hi Cammy84:

Let us look at ...

Excel Workbook
BC
1
211st
311st
422nd
533rd
644th
744th
8
Sheet8

</body></html>

You will have to incorporate this into your RANK formulation to suit.

##### MrExcel MVP
I have a column that ranks the list next to it. What I want to do is, instead of having 1,1,2,3,4,4 etc, I want it to say 1st,1st,2nd,3rd,4th,4th and so on. Just want the suffix on the end of the rank.

One of:

=B2&LOOKUP(MOD(B2,10),{0,"th";1,"st";2,"nd";3,"rd"})

where B2 houses a rank formula.

Or directly in B2:

=RANK(A2,\$A\$2:\$A\$100)&LOOKUP(MOD(RANK(A2,\$A\$2:\$A\$100),10),{0,"th";1,"st";2,"nd";3,"rd"})

#### pgc01

##### MrExcel MVP
Hi Cammy84

Not very often but..
If you have a big list you have to consider the exceptions 11,12,13 every hundred

203rd, 243rd, but 213th

#### barry houdini

##### MrExcel MVP
Perhaps [edited]

=RANK(A1,A\$1:A\$100)&LOOKUP(AND(MOD(RANK(A1,A\$1:A\$100),100)<>{11,12,13})*MOD(RANK(A1,A\$1:A\$100),10),{0,1,2,3,4;"th","st","nd","rd","th"})

Replies
1
Views
383
Replies
2
Views
235
Replies
1
Views
649
Replies
0
Views
232
Replies
7
Views
238

1,181,374
Messages
5,929,577
Members
436,680
Latest member
kellyjkon

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back