Ranking

[Tom]

I have a column that stores processing times e.g. "21:15" (twenty-one
minutes and 15 seconds -- in the formula bar, the value is displayed as
"9:15:00 PM" though).

I now want to rank the processing times from lowest to highest.

For instance, if I have the following listed 5 processing times in A1:A5, I
want to show their ranks in column B.
20:54 2
21:14 3
21:39 5
21:15 4
20:39 1

.... even better would be if I could display the following in B1:B5
2nd out of 5
3rd out of 5
5th out of 5
4th out of 5
1st out of 5

Does anyone know as to how
a) I can create such ranking formula?
b) ... which potentially includes the "st", "nd", "rd", "th"
c) ... and uses a dynamic record count number (in this case "5")


Thanks in advance,
Tom

 

[Ron Rosenfeld]

I have a column that stores processing times e.g. "21:15" (twenty-one
minutes and 15 seconds -- in the formula bar, the value is displayed as
"9:15:00 PM" though).

I now want to rank the processing times from lowest to highest.

For instance, if I have the following listed 5 processing times in A1:A5, I
want to show their ranks in column B.
20:54 2
21:14 3
21:39 5
21:15 4
20:39 1

... even better would be if I could display the following in B1:B5
2nd out of 5
3rd out of 5
5th out of 5
4th out of 5
1st out of 5

Does anyone know as to how
a) I can create such ranking formula?
b) ... which potentially includes the "st", "nd", "rd", "th"
c) ... and uses a dynamic record count number (in this case "5")

With your data in A1:A5, enter the following formula in B1, and copy/drag down
to B5:

=RANK(A1,$A$1:$A$5,1) & " out of " & COUNT($A$1:$A$5)

Modify the ranges as needed depending on the number of entries.

To include the suffixes will require a VBA routine. How much of a routine
depends on the potential range of your rankings.

Post back if that is desired.


--ron

 

[Tom]

Ron:

Thanks for your prompt reply... I appreciate it!

Although having the suffix would be great, I think your recommendation works
just fine.

One more thing though... when I drag the formula all the way down to the
table bottom, I get "#N/A" for those rows where I don't have a processing
time entered.

Is there a way to modify the function so that "#N/A's" won't show up?

 

[Biff]

To include the suffixes will require a VBA routine.

Not really. Create a table for the numbers and the
suffixes and use a lookup:

=RANK(A1,A1:A5,1)&VLOOKUP(RANK(A1,A1:A5,1),I1:J5,2,0)&"
out of "&COUNT(A1:A5)

Biff

-----Original Message-----

 

[Biff]

Hi Tom!

In an out of the way location on your sheet create a table
that lists the numbers with their suffix. I'll use I1:J5
for this example:

I J
1 st
2 nd
3 rd
4 th
5 th
etc
etc

Now, enter this formula in B1 and copy down as needed:
(adjust ranges to suit)

=IF(A1="","",RANK(A1,A$1:A$5,1)&VLOOKUP(RANK
(A1,A$1:A$5,1),I$1:J$5,2,0)&" out of "&COUNT(A$1:A$5))

Biff

-----Original Message-----
Ron:

Thanks for your prompt reply... I appreciate it!

Although having the suffix would be great, I think your recommendation works
just fine.

One more thing though... when I drag the formula all the way down to the
table bottom, I get "#N/A" for those rows where I don't have a processing
time entered.

Is there a way to modify the function so that "#N/A's" won't show up?


--
Thanks,
Tom

 

[Tom]

Biff:

Thanks for chipping in here...

okay, I updated your function to include my cell ranges...

=IF(C2="","",RANK(C2,$C$2:$C$46,1)&VLOOKUP(RANK(C2,$C$2:$C$46,1),I$2:J$12,2,
0)&" out of "&COUNT($C$2:$C$46))

conceptually, it works great; however, at this time, it concatenates the
lookup values in column I and J.

If the following values are listed in I$2:J$12:

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

then the results in column D are:

22nd out of 11
44th out of 11
1010th out of 11
55th out of 11
11st out of 11
66th out of 11
88th out of 11
1111th out of 11
77th out of 11
99th out of 11
33rd out of 11

while e.g. "22nd out of 11" should be "2nd out of 11"... however, the "2" in
column I is prefixed so it reads "22".

Do you know how I can fix that?


--
Thanks,
Tom

Hi Tom!

In an out of the way location on your sheet create a table
that lists the numbers with their suffix. I'll use I1:J5
for this example:

I J
1 st
2 nd
3 rd
4 th
5 th
etc
etc

Now, enter this formula in B1 and copy down as needed:
(adjust ranges to suit)

=IF(A1="","",RANK(A1,A$1:A$5,1)&VLOOKUP(RANK
(A1,A$1:A$5,1),I$1:J$5,2,0)&" out of "&COUNT(A$1:A$5))

Biff

-----Original Message-----
Ron:

Thanks for your prompt reply... I appreciate it!

Although having the suffix would be great, I think your recommendation works
just fine.

One more thing though... when I drag the formula all the way down to the
table bottom, I get "#N/A" for those rows where I don't have a processing
time entered.

Is there a way to modify the function so that "#N/A's" won't show up?

 

[Tom]

Biff:

Never mind... I got it... there was one too many "Ranks" in the function

I changed the function to

=IF(C2="","",VLOOKUP(RANK(C2,$C$2:$C$46,1),I$2:J$14,2,0)&" out of
"&COUNT($C$2:$C$46))

it works great now!!!

--
Thanks,
Tom

Hi Tom!

In an out of the way location on your sheet create a table
that lists the numbers with their suffix. I'll use I1:J5
for this example:

I J
1 st
2 nd
3 rd
4 th
5 th
etc
etc

Now, enter this formula in B1 and copy down as needed:
(adjust ranges to suit)

=IF(A1="","",RANK(A1,A$1:A$5,1)&VLOOKUP(RANK
(A1,A$1:A$5,1),I$1:J$5,2,0)&" out of "&COUNT(A$1:A$5))

Biff

-----Original Message-----
Ron:

Thanks for your prompt reply... I appreciate it!

Although having the suffix would be great, I think your recommendation works
just fine.

One more thing though... when I drag the formula all the way down to the
table bottom, I get "#N/A" for those rows where I don't have a processing
time entered.

Is there a way to modify the function so that "#N/A's" won't show up?

 

[Biff]

Hi Tom!

Your table is not constructed properly. Cells I2:I12
should contain the numbers. Cells J2:J12 should contain
*only* the suffix:

I2 = 1 J2 = st
I3 = 2 J3 = nd
I4 = 3 J3 = rd
I5 = 4 J4 = th
etc
etc

I can put together a little sample file and send it to you
if you'd like.

Biff

-----Original Message-----
Biff:

Thanks for chipping in here...

okay, I updated your function to include my cell ranges...

=IF(C2="","",RANK(C2,$C$2:$C$46,1)&VLOOKUP(RANK (C2,$C$2:$C$46,1),I$2:J$12,2,
0)&" out of "&COUNT($C$2:$C$46))

conceptually, it works great; however, at this time, it concatenates the
lookup values in column I and J.

If the following values are listed in I$2:J$12:

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

then the results in column D are:

22nd out of 11
44th out of 11
1010th out of 11
55th out of 11
11st out of 11
66th out of 11
88th out of 11
1111th out of 11
77th out of 11
99th out of 11
33rd out of 11

while e.g. "22nd out of 11" should be "2nd out of 11"... however, the "2" in
column I is prefixed so it reads "22".

Do you know how I can fix that?

 

[Ron Rosenfeld]

Not really. Create a table for the numbers and the
suffixes and use a lookup:

=RANK(A1,A1:A5,1)&VLOOKUP(RANK(A1,A1:A5,1),I1:J5,2,0)&"
out of "&COUNT(A1:A5)

Sure. That'd work. But you would need to set up an entry for every possible
rank.

I would use a VBA routine that might be more generally useful. For example:

=============================
Function OrdinalNum(num) As String
Dim Suffix As String

OrdinalNum = num
If Not IsNumeric(num) Then Exit Function
If num <> Int(num) Then Exit Function

Select Case num Mod 10
Case Is = 1
Suffix = "st"
Case Is = 2
Suffix = "nd"
Case Is = 3
Suffix = "rd"
Case Else
Suffix = "th"
End Select

Select Case num Mod 100
Case 11 To 19
Suffix = "th"
End Select

OrdinalNum = Format(num, "#,###") & Suffix
End Function
==================================


--ron

 

[Biff]

Hi Ron!

That looks pretty good but exactly how would you use that
in this situation? Like this:

=OrdinalNum(RANK(A1,A1:A5,1)).........

Biff

-----Original Message-----

 

[Ron Rosenfeld]

That looks pretty good but exactly how would you use that
in this situation? Like this:

=OrdinalNum(RANK(A1,A1:A5,1)).........

If the data is in a range named rng, encompassing A1:An, then

=IF(A1="","",ordinalnum(RANK(A1,rng,1)) & " out of "&COUNT(rng))

although

=IF(rng="","",OrdinalNum(RANK(rng,rng,1)) & " out of "&COUNT(rng))

seems to work also.

and copy/drag it down as needed.


--ron

 

[Tom]

Ron:

Not sure if I'm following you...

I copied your VBA code, then right-clicked on the worksheet and pasted the
code into the VBA window.... saved and reopened file.

Lastly, I copied the "=OrdinalNum(RANK(C2,C2:C12,1))" into cell D2. I now
see "#NAME?" in the cell D2.

What am I doing wrong?

 

[Tom]

Biff:

Yep, I would appreciate the sample file...

please send it to skydiveREMOVETHIStom@erolsREPLACEWITHDOTcom

please remove the "REMOVETHIS" and replace the 'REPLACEWITHDOT" with "."

 

[Ron Rosenfeld]

Ron:

Not sure if I'm following you...

I copied your VBA code, then right-clicked on the worksheet and pasted the
code into the VBA window.... saved and reopened file.

Lastly, I copied the "=OrdinalNum(RANK(C2,C2:C12,1))" into cell D2. I now
see "#NAME?" in the cell D2.

What am I doing wrong?

Tom,

The code needs to be entered into a module. So:

1. <alt><F11> opens the Visual Basic Editor.
2. Ensure your project is highlighted in the Project Explorer Window.
3. Insert/Module (main menu bar)
4. Then Paste the code into *that* window.

Then you can use it as a worksheet function. If you hit the Fx key next to the
formula bar, and select User Defined functions, you will see it on the list,
there. (Although that is not necessary to use it).


--ron

 

[Tom]

Ron,

that works superb!!! Thanks so much for providing the code to me!

BTW, what's the difference between my way of copying it and yours via using
a module?

 

[Ron Rosenfeld]

that works superb!!! Thanks so much for providing the code to me!

You're welcome. Thank you for the feedback

BTW, what's the difference between my way of copying it and yours via using
a module?

I'm not certain how to explain it, and I probably don't really understand. But
I believe it has to do with scope and visibility.


--ron