# Modified Rank function?

#### Juggler_IN

##### Board Regular
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``````

#### Akuini

##### Well-known Member
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]``

#### Juggler_IN

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

#### Juggler_IN

##### Board Regular
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]``

#### jasonb75

##### Well-known Member
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:

#### Juggler_IN

##### Board Regular
@jasonb75; UDF edit not working.

#### jasonb75

##### Well-known Member
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

##### Board Regular
@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.