# 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 ?

#1
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 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.

Report •

#2
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