# Thread: Custom format for rankings

1. Is it possible to create a custom format for rankings, so that the result of the rank formula remains a value, but shows as 1st, 2nd, 3rd, 4th etc.....

Thanks

2. No, you can't do that with a single custom number format.

You could use code to apply a different custom number format to each cell according to its value.
Or you could use formulas to place the suffix "st", "nd", "rd" or "th" in the cells next to the numbers.

3. [quote name='VegasNath' post='799612' date='23-Oct-2009 23:37']Is it possible to create a custom format for rankings, so that the result of the rank formula remains a value, but shows as 1st, 2nd, 3rd, 4th etc.....

Thanks[/quote]

Not a Format, but you could calculate with a formula similar to this

=RANK(E2,\$E\$2:\$E\$81,0) & VLOOKUP(VALUE(RIGHT(RANK(E2,\$E\$2:\$E\$81,0),1)),\$AA\$ 2:\$AB\$11,2,0)

Where here the lookup range \$AA2:\$AB11 contains

OR

Where AD2 Contains th, AE2 Contains st etc
Obviously you need to modify the referenced ranges

0 th
1 st
2 nd
3 rd
4 th
5 th
6 th
7 th
8 th
9 th

4. Oh, that's a shame!

Adding a column for the suffix would be more hassle than it's worth in this situation, and using code would probably be overkill here also.

Thanks for the suggestions though Hans.

5. Hi Andrew, you'd need to treat numbers ending in 11, 12 or 13 separately (you want 11th, 12th, 13th instead of 11st, 12nd, 13rd).

6. [quote name='HansV' post='799618' date='24-Oct-2009 00:05']Hi Andrew, you'd need to treat numbers ending in 11, 12 or 13 separately (you want 11th, 12th, 13th instead of 11st, 12nd, 13rd).[/quote]

Jolly Good Point Hans, it must be getting late over here.

Time for some sleep.

7. Custom number formats aren't flexible enough to allow for 4 different suffixes.

Just to give you an idea, I have attached a workbook where custom number formats were applied using the following code:

Code:
```Sub FormatRanks()
Dim oCell As Range
For Each oCell In Selection
If IsNumeric(oCell.Value) Then
oCell.NumberFormat = "0" & Chr(34) & Suffix(oCell.Value) & Chr(34)
End If
Next oCell
End Sub

Function Suffix(n As Long) As String
Select Case n Mod 100
Case 11, 12, 13
Suffix = "th"
Case Else
Select Case n Mod 10
Case 1
Suffix = "st"
Case 2
Suffix = "nd"
Case 3
Suffix = "rd"
Case Else
Suffix = "th"
End Select
End Select
End Function```
The FormatRanks macro operates on the currently selected cells, but you can easily adapt it to operate on any range.

8. Thanks both. I was trying to adapt Andrews formula when I too started to wonder about the 11th, 12th etc, so came back to ask, and can see that Hans has addressed that point. Thanks for trying Andrew!

Hans: Thanks very much, I will definately use that code, as you state that there is no formula alternative.

9. I Might be wrong again, it is getting later, but something like this might also
do it

Where starting in AD2 there is a row containing

th st nd rd th th th th th th

=RANK(E2,\$E\$2:\$E\$129,0) & IF(OR(VALUE(RIGHT(RANK(E2,\$E\$2:\$E\$129,0),2))=11,VA LUE(RIGHT(RANK(E2,\$E\$2:\$E\$129,0),2))=12,VALUE(RIGH T(RANK(E2,\$E\$2:\$E\$129,0),2))=13),"th",OFFSET(\$AD\$2 ,0,VALUE(RIGHT(RANK(E2,\$E\$2:\$E\$129,0),1))))

Seems OK, but a bit of a stinker.

10. [quote name='VegasNath' post='799625' date='24-Oct-2009 01:25']as you state that there is no formula alternative.[/quote]
You can't use a single custom number format, but you *can* use formulas, such as the one posted by Andrew, or somewhat shorter

=A1&IF(OR(MOD(A1,100)={11,12,13}),"th",CHOOSE(MIN( 5,RIGHT(A1)+1),"th","st","nd","rd","th"))

(From: 1st, 2nd, 3rd etc. - Excel Help Forum)

11. [quote name='HansV' post='799628' date='24-Oct-2009 00:34']You can't use a single custom number format, but you *can* use formulas, such as the one posted by Andrew, or somewhat shorter

=A1&IF(OR(MOD(A1,100)={11,12,13}),"th",CHOOSE(MIN( 5,RIGHT(A1)+1),"th","st","nd","rd","th"))

(From: 1st, 2nd, 3rd etc. - Excel Help Forum)[/quote]

I like that one very slick.

It makes mine

=RANK(E2,\$E\$2:\$E\$129,0) & IF(OR(VALUE(RIGHT(RANK(E2,\$E\$2:\$E\$129,0),2))={11,1 2,13}),"th",OFFSET(\$O\$2,0,VALUE(RIGHT(RANK(E2,\$E\$2 :\$E\$129,0),1))))

The ability to do a comparison Left Side = {ar1,ar2,ar3,ar4...} I did not realise you could do.

The whole of life is a learning experience.

12. Thanks to you both for your time on this.

=A1&IF(OR(MOD(A1,100)={11,12,13}),"th",CHOOSE(MIN( 5,RIGHT(A1)+1),"th","st","nd","rd","th"))

How can I combine this with formula's such as:

=SUMPRODUCT((\$B7=\$B\$7:\$B\$363)*(H7<H\$7:H\$363))+1

and

=RANK(H7,H\$7:H\$363)

13. Replace the A1s in the fromula with your current formula.

For example:
=RANK(H7,H\$7:H\$363)&IF(OR(MOD(A1,100)={11,12,13}), "th",CHOOSE(MIN(5,RIGHT(RANK(H7,H\$7:H\$363))+1),"th ","st","nd","rd","th"))

14. [quote name='mbarron' post='799637' date='24-Oct-2009 03:31']Replace the A1s in the fromula with your current formula.

For example:
=RANK(H7,H\$7:H\$363)&IF(OR(MOD(A1,100)={11,12,13}), "th",CHOOSE(MIN(5,RIGHT(RANK(H7,H\$7:H\$363))+1),"th ","st","nd","rd","th"))[/quote]

Thanks very much!

I've run into a little problem. Using the formula below, I am now getting #value! errors when performing seperate calculations based on the rankings.

Code:
`=RANK(F3,F\$3:F\$173)&IF(OR(MOD(RANK(F3,F\$3:F\$173),100)={11,12,13}),"th",CHOOSE(MIN(5,RIGHT(RANK(F3,F\$3:F\$173))+1),"th","st","nd","rd","th"))`
I need to calculate the movement between 2 ranking columns: =Q3-H3. I tried =value(Q3)-value(H3) without success.

15. =LEFT(Q3,3)-LEFT(H3,3)

This works where both ranks are between 100 and 999, but I need it for all numbers, thus ignoring the 2 last characters?

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•