Remark Marks Rank Pass 495 1

Fail 490 -

Pass 480 2

Pass 475 3

Fail must be ignored from ranking What is the formula ?

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 Next '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 Next End With End Sub

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

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.

Ask Your Question

Weekly Poll