Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Bronze Lounger
    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

  2. #2
    Plutonium Lounger
    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.

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    [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

    =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 th
    Andrew

  4. #4
    Bronze Lounger
    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.

  5. #5
    Plutonium Lounger
    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).

  6. #6
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    [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.
    Andrew

  7. #7
    Plutonium Lounger
    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
    The FormatRanks macro operates on the currently selected cells, but you can easily adapt it to operate on any range.
    Attached Files Attached Files

  8. #8
    Bronze Lounger
    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.

  9. #9
    5 Star Lounger AndrewKKWalker's Avatar
    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

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [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. #11
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    [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.
    Andrew

  12. #12
    Bronze Lounger
    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)

  13. #13
    5 Star Lounger
    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"))

  14. #14
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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. #15
    Bronze Lounger
    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?

Page 1 of 2 12 LastLast

Posting Permissions

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