Modified Rank function?

[Juggler_IN]

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.

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

 

[Akuini]

Try using "not exact match" in the Match Fuction (i.e 1) & remove the + 1

Rnk = Application.WorksheetFunction.Match(x, .ToArray, 1) '[COLOR=#ff0000]''+ 1[/COLOR]

 

[Juggler_IN]

@Akuini; It is working for Ascending but not for Descending.

 

[Juggler_IN]

Also, there is a typo in my original code:

[COLOR=#333333]        Rnk = Application.WorksheetFunction.Match(x, .ToArray, 0#) + 1[/COLOR]

Should be,

[COLOR=#333333]        Rnk = Application.WorksheetFunction.Match(x, .ToArray, 0#)[/COLOR]

 

[jasonb75]

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

Rnk = Application.WorksheetFunction.Match(x, .ToArray, IIf(z = False, -1, 1))

 

[Juggler_IN]

@jasonb75; UDF edit not working.

 

[jasonb75]

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.

 

[Juggler_IN]

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