Ranking 'off sheet'

[Guest]

TIA for your hel
I have a 1 dimension array loaded with numbers
I would like to load another array with the numerical ranks of the elements of the first array. Would like to assign 'true ranks' ie 2 numbers tied at 2nd place = 2.5. Also would like to steer clear of worksheetfunction.rank (to keep the process 'off sheet' in the interests of speeding up th process
Any suggestions wil be much appreciated

 

[Bernie Deitrick]

David,

See the example code below for one way.

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim myVal(1 To 10) As Integer
Dim myRank(1 To 10) As Double
Dim i As Integer
Dim j As Integer

'Populate a test array
For i = 1 To 3
myVal(i) = i
Next i
For i = 4 To 10
myVal(i) = 11 - i
Next i

'Do the ranking
For i = LBound(myVal) To UBound(myVal)
myRank(i) = 1
For j = LBound(myVal) To UBound(myVal)
If i <> j Then
If myVal(i) < myVal(j) Then
myRank(i) = myRank(i) + 1
ElseIf myVal(i) = myVal(j) Then
myRank(i) = myRank(i) + 0.5
End If
End If
Next j
Next i

'Show the values and rank results
Dim msg As String
msg = ""
For i = 1 To 10
msg = msg & myVal(i) & ": " & myRank(i) & Chr(10)
Next i

MsgBox msg

End Sub

TIA for your help
I have a 1 dimension array loaded with numbers.
I would like to load another array with the numerical ranks of the

elements of the first array. Would like to assign 'true ranks' ie 2 numbers
tied at 2nd place = 2.5. Also would like to steer clear of
worksheetfunction.rank (to keep the process 'off sheet' in the interests of
speeding up th process)