Find Rank

Robin_

New Member
Joined
Aug 29, 2014
Messages
13
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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Name[/TD]
[TD="align: center"]Total[/TD]
[TD="align: center"]Result[/TD]
[TD="align: center"]Rank[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD="align: center"]89[/TD]
[TD="align: center"]PASS[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DEF[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]FAIL[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GHI[/TD]
[TD="align: center"]85[/TD]
[TD="align: center"]PASS[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JKL[/TD]
[TD="align: center"]95[/TD]
[TD="align: center"]PASS[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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)
 
Upvote 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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 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

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.
 
Upvote 0
Maybe...


[Table="class: grid"][tr][td] [/td][td]
A
[/td][td]
B
[/td][td]
C
[/td][td]
D
[/td][/tr]
[tr][td]
1
[/td][td]
Name​
[/td][td]
Total​
[/td][td]
Result​
[/td][td]
Rank​
[/td][/tr]
[tr][td]
2
[/td][td]
ABC​
[/td][td]
89​
[/td][td]
PASS​
[/td][td]
2​
[/td][/tr]
[tr][td]
3
[/td][td]
DEF​
[/td][td]
45​
[/td][td]
FAIL​
[/td][td]
No Rank​
[/td][/tr]
[tr][td]
4
[/td][td]
GHI​
[/td][td]
85​
[/td][td]
PASS​
[/td][td]
3​
[/td][/tr]
[tr][td]
5
[/td][td]
JKL​
[/td][td]
95​
[/td][td]
PASS​
[/td][td]
1​
[/td][/tr]
[/table]


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

Hope this helps

M.
 
Upvote 0
Maybe...


[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]
A
[/TD]
[TD]
B
[/TD]
[TD]
C
[/TD]
[TD]
D
[/TD]
[/TR]
[TR]
[TD]
1
[/TD]
[TD]
Name​
[/TD]
[TD]
Total​
[/TD]
[TD]
Result​
[/TD]
[TD]
Rank​
[/TD]
[/TR]
[TR]
[TD]
2
[/TD]
[TD]
ABC​
[/TD]
[TD]
89​
[/TD]
[TD]
PASS​
[/TD]
[TD]
2​
[/TD]
[/TR]
[TR]
[TD]
3
[/TD]
[TD]
DEF​
[/TD]
[TD]
45​
[/TD]
[TD]
FAIL​
[/TD]
[TD]
No Rank​
[/TD]
[/TR]
[TR]
[TD]
4
[/TD]
[TD]
GHI​
[/TD]
[TD]
85​
[/TD]
[TD]
PASS​
[/TD]
[TD]
3​
[/TD]
[/TR]
[TR]
[TD]
5
[/TD]
[TD]
JKL​
[/TD]
[TD]
95​
[/TD]
[TD]
PASS​
[/TD]
[TD]
1​
[/TD]
[/TR]
</tbody>[/TABLE]


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.
 
Upvote 0
Ohh sorry....
If we exclude that part, then it won't work as required.

Thank you so much.
 
Upvote 0

Forum statistics

Threads
1,224,427
Messages
6,178,586
Members
452,860
Latest member
jroberts02

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?

Disable AdBlock

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
Back
Top