Find Rank

[Robin_]

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>

Thanks in advance.

 

[steve the fish]

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)

 

[Special-K99]

Try

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

 

[Robin_]

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

 

[iliauk]

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?

 

[Robin_]

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.

 

[Marcelo Branco]

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.

 

[Robin_]

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.

 

[Robin_]

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

Thank you so much.

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.