Ranking 'off sheet'
 

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

Ranking 'off sheet'
 

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



"David" wrote in message
...
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)
Any suggestions wil be much appreciated