# Find Rank

#### Robin_

##### New Member
How to find the rank of only passed students.
Here is a part of Mark sheet I prepared.
I need to find the rank of only passed students.

That means, in my problem,
ABC should get 2
DEF should get 0 or "FAIL" or "No Rank"
GHI should get 3
and JKL should get 1

In this problem, there is no incidence of tie. But, in my real problem, there can occur tie. And, for tie, the rank version of 1, 2, 3, 3, 5, 6 is okay.

 Name Total Result Rank ABC 89 PASS DEF 45 FAIL GHI 85 PASS JKL 95 PASS

<tbody>
</tbody>

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Presuming that a low score is a fail then this works. If not slightly different approach needed.

=IF(C2="PASS",RANK(B2,\$B\$2:\$B\$100),0)

Try

=IF(B1>50,RANK(B1,B\$1:B\$4),"")
where 50 is a pass rate (adjust as necessary)
and copy down the column

Thank you.

But, both of the solutions are not alike my need. The both formula will take into account the Failed students marks. But, I don't want that.
I want to rank only Passed students. :D

Surely rank will be: 1, ..., rank of last pass, rank of first fail, ..., rank of last fail.

Since both formulas ignore the fails; and these come at the end then it makes no difference?

Try

=IF(B1>50,RANK(B1,B\$1:B\$4),"")
where 50 is a pass rate (adjust as necessary)
and copy down the column

No No...
The total column is the sum of different subjects not shown.
Let's say, there are 5 subjects each of Full marks 20. The student must get 50% marks in each of the subjects to pass.

Now, there is possibility of a failed student to have more than 50 marks in total.
Similarly, there is possibility of a passed student to score exactly 50 marks in total.

Now, I need to discard any marks more than 50 which is scored by a failed student.

Or, It will be okay, if the rank of failed students will come after the ranking of passed students as mentioned by iliauk (#5)

I hope, now I made it clear.

Maybe...

 A​ B​ C​ D​ 1​ Name​ Total​ Result​ Rank​ 2​ ABC​ 89​ PASS​ 2​ 3​ DEF​ 45​ FAIL​ No Rank​ 4​ GHI​ 85​ PASS​ 3​ 5​ JKL​ 95​ PASS​ 1​

Formula in D2 copied down
=IF(C2="FAIL","No Rank",COUNTIFS(B:B,">"&B2,C:C,C2)+1)

Hope this helps

M.

Maybe...

 A​ B​ C​ D​ 1​ Name​ Total​ Result​ Rank​ 2​ ABC​ 89​ PASS​ 2​ 3​ DEF​ 45​ FAIL​ No Rank​ 4​ GHI​ 85​ PASS​ 3​ 5​ JKL​ 95​ PASS​ 1​

<tbody>
</tbody>

Formula in D2 copied down
=IF(C2="FAIL","No Rank",COUNTIFS(B:B,">"&B2,C:C,C2)+1)

Hope this helps

M.

Thank you.
That matches my requirement.
Still, is there any purpose of second criteria ",C:C,C2" in the formula???
If we exclude it then too, it works fine.
Confused.

Ohh sorry....
If we exclude that part, then it won't work as required.

Thank you so much.

You are welcome. Thanks for the feedback.

M.

Replies
3
Views
421
Replies
1
Views
249
Replies
1
Views
689
Replies
2
Views
358
Replies
5
Views
675

1,196,307
Messages
6,014,570
Members
441,828
Latest member
cofracr

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back