conditional rank Tell me the fromula ?

March 19, 2012 at 12:15:40
Specs: Windows 7
Remark Marks Rank

Pass 495 1
Fail 490 -
Pass 480 2
Pass 475 3

Fail must be ignored from ranking What is the formula ?

See More: conditional rank Tell me the fromula ?

Report •

March 19, 2012 at 18:21:54
There is no built in formula that will eliminate certain values in a range and then rank the rest.

You'll need to use a macro.

Assuming your table begins in A1, this macro should provide the Ranking that you are looking for with the inclusion of one problem:

It will not work correctly if there will be "ties" in your list of Marks.

Ties will create a problem and require code that might get pretty complicated since the RANK function doesn't work on Arrays.

Let me know if Ties are a possibility and I'll see what I can do.

Option Base 1
Sub PassingRank()
'Declare Array
Dim PassArray() As Integer
'Determine length of data based on Column A
  lastRw = Range("A" & Rows.Count).End(xlUp).Row
'Dimension Array pass on count of "Pass"
  numPass = WorksheetFunction.CountIf(Range("A2:A" & lastRw), "=Pass")
ReDim PassArray(numPass)
'Clear Column C before each run
  Range("C2:C" & lastRw).ClearContents
'Populate array with only Passing Marks
   For rw = 2 To lastRw
     If Range("A" & rw) = "Pass" Then
      m = m + 1
       PassArray(m) = Range("B" & rw)
     End If
'Use the LARGE function to Rank the array elements
'Find each element in Column B and put it's rank next to it
 With Range("B2:B" & lastRw)
  For mRank = 1 To UBound(PassArray)
   Set m = .Find(WorksheetFunction.Large(PassArray, mRank), lookat:=xlWhole)
    If Not m Is Nothing Then
      Range("C" & m.Row) = mRank
    End If
 End With
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

March 20, 2012 at 06:51:05
Here's a way to do it without a macro, although a macro could be written to make this more automatic.

This method will handle Ties in the normal way that the RANK function deals with Ties.

1 - Insert a "helper column" in Column A and fill it with 1, 2, 3, etc. down to the bottom of your list.
2 - Select all of your data, including Column A and Sort it based on the Remark column in Descending order. This will bring all of your Pass cells to the top.
3 - Use the RANK function to RANK only the Pass section of your data.
4 - Copy the RANK results and do a PasteSpecial..Values to lock in the ranking values.
5 - Select all of your data (including Column A and the Rank column) and sort it on Column A in Ascending order. This will put the data back into the original order.
6 - Delete the helper column, Column A.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

Ask Question