Results 1 to 15 of 29
Thread: Custom format for rankings

20091023, 18:37 #1
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20091023, 18:47 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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.

20091023, 18:57 #3
 Join Date
 Apr 2001
 Location
 Cambridge, UK
 Posts
 1,020
 Thanks
 0
 Thanked 3 Times in 3 Posts
[quote name='VegasNath' post='799612' date='23Oct2009 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
=RANK(E2,$E$2:$E$81,0) & OFFSET($AD$2,0,VALUE(RIGHT(RANK(E2,$E$2:$E$81,0),1 )))
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 thAndrew

20091023, 19:05 #4
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20091023, 19:05 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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).

20091023, 19:08 #6
 Join Date
 Apr 2001
 Location
 Cambridge, UK
 Posts
 1,020
 Thanks
 0
 Thanked 3 Times in 3 Posts
[quote name='HansV' post='799618' date='24Oct2009 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.Andrew

20091023, 19:10 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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

20091023, 19:25 #8
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20091023, 19:26 #9
 Join Date
 Apr 2001
 Location
 Cambridge, UK
 Posts
 1,020
 Thanks
 0
 Thanked 3 Times in 3 Posts
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.Andrew

20091023, 19:34 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
[quote name='VegasNath' post='799625' date='24Oct2009 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)

20091023, 19:38 #11
 Join Date
 Apr 2001
 Location
 Cambridge, UK
 Posts
 1,020
 Thanks
 0
 Thanked 3 Times in 3 Posts
[quote name='HansV' post='799628' date='24Oct2009 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.Andrew

20091023, 22:20 #12
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
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)

20091023, 22:31 #13
 Join Date
 Aug 2004
 Location
 Connecticut, USA
 Posts
 816
 Thanks
 0
 Thanked 0 Times in 0 Posts
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"))

20091024, 11:07 #14
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='mbarron' post='799637' date='24Oct2009 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"))

20091024, 11:18 #15
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
=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?