Modified Rank function?

Juggler_IN

Active Member
Joined
Nov 19, 2014
Messages
347
Office Version
  1. 2003 or older
Platform
  1. Windows
I have worked out a UDF which replicates Excel Rank() function. Now, I am trying to modify it such that instead of outputting 1 2 2 4 as the ranks for a set {2,5,5,8} it should output 1 3 3 4; and it should be able to handle any data as per the modified logic.

Code:
Public Function Rnk( _
       ByVal x As Variant, _
       ByRef y As Variant, _
       ByVal z As Boolean) As Variant

' x is the item, y is the Range, and z is the rank order.


    If TypeName(y) = "Range" Then
        y = y.Value2
    Else
    End If


    With CreateObject("System.Collections.ArrayList")


        Dim i As Variant
        For Each i In y
            If VarType(i) >= 2# And VarType(i) <= 6# Then .Add Val(i)
        Next i


        .Sort


        If z = False Then    ' Descending Order
            .Reverse
        Else    ' Ascending Order
        End If


        Rnk = Application.WorksheetFunction.Match(x, .ToArray, 0#) + 1


    End With


End Function
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Try using "not exact match" in the Match Fuction (i.e 1) & remove the + 1

Code:
Rnk = Application.WorksheetFunction.Match(x, .ToArray, 1) '[COLOR=#ff0000]''+ 1[/COLOR]
 
Upvote 0
Also, there is a typo in my original code:
Code:
[COLOR=#333333]        Rnk = Application.WorksheetFunction.Match(x, .ToArray, 0#) + 1[/COLOR]
Should be,
Code:
[COLOR=#333333]        Rnk = Application.WorksheetFunction.Match(x, .ToArray, 0#)[/COLOR]
 
Upvote 0
Do you really need a udf for this?

For ascending order,

Original logic =COUNTIFS($A$2:$A$5,"<"&A2)+1
New logic =COUNTIFS($A$2:$A$5,"<="&A2)

change < to > for descending.

edit:- for the udf, try
Code:
Rnk = Application.WorksheetFunction.Match(x, .ToArray, IIf(z = False, -1, 1))
 
Last edited:
Upvote 0
Repeating my previous question which you appear to have overlooked, Do you really need a udf?

You can do what you want with native excel functions, there is no point in trying to re-invent the wheel!

Bearing in mind that I can not read your mind or see your screen! What does 'not working' mean? Your logic is illogical so False might need to changing to True.

Runtime error? Wrong results? Something else?

Your logic is illogical so False might need to changing to True.
 
Upvote 0
@jasonb75;

With z = False, it "still" gives 4 2 2 1 as the output instead of 4 3 3 1.
With z = True, it gives 1 3 3 4 as the output which is desired.
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,728
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top