|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
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")
'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)
'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
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.