Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ranking from one sheet to another | Excel Worksheet Functions | |||
top ten ranking | Excel Discussion (Misc queries) | |||
Ranking Question Listing Same Sheet! | Excel Discussion (Misc queries) | |||
Ranking | Excel Worksheet Functions | |||
Ranking | Excel Discussion (Misc queries) |