0

I have found an answer to the question "How to assign a rank number to an array when ties exist" in Php and (looked lilke) C++. I also found a couple of answers about Excel's PercentRank in language I do not know.

Can someone help me to do this in VBA? I need to calculate the PercentRank from 12 values in Access fopr a report and I cannot use Excel. Here's an example of what I am after:

   Per  Val %Rank
    01  80  0.82
    02  74  0.45
    03  88  1.00
    04  60  0.00
    05  86  0.91
    06  68  0.18
    07  64  0.09
    08  78  0.64
    09  76  0.55
    10  72  0.27
    11  78  0.64
    12  72  0.27

Note that for the period 08 and 11 the value is the same. Also for the period 10 and 12. I read somewhere that when there are ties the function must calculate an average of somesort.

Would anyone please help with function written in VBA?

Thanks a bunch.

D. Lamarche

Daniel Lamarche
  • 1
  • 1
  • 1

1 Answers1

1

Ties should produce the same percentage rank, just like your example shows. If your x number is not in the array, then you have to extrapolate. If you're assured that x is in array, you can simplify to this

Public Function PRank(vaArray As Variant, x As Variant) As Double

    Dim lLower As Long
    Dim lHigher As Long
    Dim i As Long

    For i = LBound(vaArray, 1) To UBound(vaArray, 1)
        If vaArray(i, 1) < x Then
            lLower = lLower + 1
        ElseIf vaArray(i, 1) > x Then
            lHigher = lHigher + 1
        End If
    Next i

    PRank = lLower / (lLower + lHigher)

End Function

If you pass an x values that is not in vaArray, this will produce the wrong result. Also, this assumes you're passing a two dimensional array (like an Excel column would be), so you may need to adjust for that.

Dick Kusleika
  • 32,673
  • 4
  • 52
  • 73