Custom format for rankings

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

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

Reply | Quote

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).

Reply | Quote

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).

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

Time for some sleep.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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,VALUE(RIGHT(RANK(E2,$E$2:$E$129,0),2))=12,VALUE(RIGHT(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.

Reply | Quote

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.

Reply | Quote

as you state that there is no formula alternative.

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)

Reply | 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)

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,12,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.

Reply | Quote

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)

Reply | Quote

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”))

Reply | Quote

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”))

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.

Reply | Quote

That’s why it would be better to either use code to create custom formats, or to calculate the ranks in one column (suitable for calculations) and to use formulas in another column for the display values.

But to ignore the last two characters in your situation, use

=LEFT(Q3,LEN(Q3)-2)-LEFT(H3,LEN(H3)-2)

Reply | Quote

That’s why it would be better to either use code to create custom formats, or to calculate the ranks in one column (suitable for calculations) and to use formulas in another column for the display values.

Thanks, I’m starting to wish I had now…..

One final question if I may:

Code:

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

This works, some cells display correctly, and some display decimalised to many places, even though the cells are formatted to number without decimals. Can this be adapted to output to 0 decimal places?

Reply | Quote

You could use the ROUND function:

=ROUND(AVERAGE(M20:IV20),0)&IF(OR(MOD(ROUND(AVERAGE(M20:IV20),0),100)={11,12,13}),”th”,CHOOSE(MIN(5,RIGHT(ROUND(AVERAGE(M20:IV20),0))+1),”th”,”st”,”nd”,”rd”,”th”))

Reply | Quote

=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?

Reply | Quote

Thankyou Hans.

Reply | Quote

Ok, I’m back, and in reverse mode! I adopted the formula approach, and later realised that was not appropriate for calculations and charts etc.

So, I am changing tactic, and switching to Hans suggestion of using code to apply the custom format, but as usual , I need a little help.

I am not getting the expected result from the following:

Code:

	For Each t In x
		t.Offset(0, 7).NumberFormat = "0" & Chr(34) & Suffix(t.Offset(0, 7).Value) & Chr(34)
		t.Offset(0, 9).NumberFormat = "0" & Chr(34) & Suffix(t.Offset(0, 9).Value) & Chr(34)
		t.Offset(0, 11).NumberFormat = "0" & Chr(34) & Suffix(t.Offset(0, 11).Value) & Chr(34)
	Next t

You will notice that I removed the If statements from Hans code, however, I was not getting the expected result then either. Any idea’s?

Reply | Quote

I am not getting the expected result from the following:

doesn’t tell us anything specific. Details, details, details!

Reply | Quote

doesn’t tell us anything specific. Details, details, details!

Details, details, details……..

The data is back to numbers, stepping through the code:, runs as expected, but does not change the formats. Selecting the cells and running your macro does work for me, but i need to combine that with my existing code, preferably without selection.

Reply | Quote

What kind of values do t.Offset(0, 7) etc. contain?

(It is going to take a long, long time this way!)

Reply | Quote

What kind of values do t.Offset(0, 7) etc. contain?

(It is going to take a long, long time this way!)

=RANK(F4,F$4:F$174)
=SUMPRODUCT(($C4=$C$4:$C$174)*(F4<F$4:F$174))+1
=SUMPRODUCT(($D4=$D$4:$D$174)*(F4<F$4:F$174))+1

Each time the results of those formula's change, I need to update the suffix.

Reply | Quote

=RANK(F4,F$4:F$174)
=SUMPRODUCT(($C4=$C$4:$C$174)*(F4<F$4:F$174))+1
=SUMPRODUCT(($D4=$D$4:$D$174)*(F4<F$4:F$174))+1

Each time the results of those formula's change, I need to update the suffix.

Then you need to run the code on a change worksheet event

Reply | Quote

You could use the Worksheet_Calculate event to update the number formats each time the formulas are updates. But that might have a serious impact on performance.

I think I’d do what I suggested higher up in this thread: “to calculate the ranks in one column (suitable for calculations) and to use formulas in another column for the display values”. The column with the ranks could be hidden.

Reply | Quote

Ok, I’m back, and in reverse mode! I adopted the formula approach, and later realised that was not appropriate for calculations and charts etc.

So, I am changing tactic, and switching to Hans suggestion of using code to apply the custom format, but as usual , I need a little help.

I am not getting the expected result from the following:

Code:

	For Each t In x
		t.Offset(0, 7).NumberFormat = "0" & Chr(34) & Suffix(t.Offset(0, 7).Value) & Chr(34)
		t.Offset(0, 9).NumberFormat = "0" & Chr(34) & Suffix(t.Offset(0, 9).Value) & Chr(34)
		t.Offset(0, 11).NumberFormat = "0" & Chr(34) & Suffix(t.Offset(0, 11).Value) & Chr(34)
	Next t

You will notice that I removed the If statements from Hans code, however, I was not getting the expected result then either. Any idea’s?

What do you get?

And what is in the target columns?

Reply | Quote

I figured it out. The code that I run has calculation set to manual, a calculate command prior to updating the suffix was required.

Thanks

Reply | Quote

The code that I run has calculation set to manual

That was another detail you omitted to tell us about…

Reply | Quote