Finding rank of passed students excluding the failed ones in Mark-sheet.

[sooshil]

I have prepared Mark-Sheet of the students appeared in an exam. I have successfully calculated Total, Percentage, Division, Result (Pass/Fail). I wanted to calculate Rank of the all the passed students.
Since my sheet contains all the the rows (Passed as well as failed students) mixed up.
=Range(number,ref,[order])
Range function asks me for the reference and which I selected all the total marks of all students. But, I need to exclude the failed students' total as they couldn't be awarded any rank.
So, how can I do that? Please help

Thank you.

 

[Namestaken]

There is a really simple, but not so beautiful, way to do this. I would make another row (we'll say column C for this example) with the following formula in C1:

=IF(A1 = "FAIL", "", B1)

In this example, A1 is the first row in the results column, FAIL is the value used in the results row for fail, and B1 is the first row in the column you are using to rank the students. Your RANK() (I assume you meant Rank and not Range in your post) function will then be:

=RANK(C1, $C$1:$C$9999999, 0)

Where C1 is the first row in the newly created column, $C$1:$C$9999999 is the length of the column (I'd recommend using a named range here that adjusts with the number of students entered), and 0 directing it to use descending order (1 for ascending which is not relevant for this ranking).

The formula we created will output #VALUE! in your rank row for all failing students and will properly rank the rest of the students. You can use a conditional format to hide the #VALUE! cells using a formula such as this:

=IF(ISERR($AA:$AA) = TRUE, TRUE, FALSE)

And setting the text color of this conditional format to the color of your background (white, by default). Also feel free to hide the newly created row.

 

[mfexcel]

Do you mean RANK function?

Anyway, there should be a failing score where any score at or below the failing score is defined as FAIL, right?

you may try something like:
=IF(Result="Fail","",RANK(score,RangeForScore))
'assume you rank the score in descending order
Pls adjust the reference/Range for Result, Score, and RangeForScore accordingly.

I have prepared Mark-Sheet of the students appeared in an exam. I have successfully calculated Total, Percentage, Division, Result (Pass/Fail). I wanted to calculate Rank of the all the passed students.
Since my sheet contains all the the rows (Passed as well as failed students) mixed up.
=Range(number,ref,[order])
Range function asks me for the reference and which I selected all the total marks of all students. But, I need to exclude the failed students' total as they couldn't be awarded any rank.
So, how can I do that? Please help

Thank you.

 

[Namestaken]

=IF(Result="Fail","",RANK(score,RangeForScore))

I believe the issue with this solution is that the RangeForScore is used to generate the rank so even if the failed students are not displayed, their ranks are still calculated which does not produce the desired results.

 

[mfexcel]

Say the score from 20 to 100 and the pass score is 50.

Although the RANK function ranks every score (including those failing scores), the ranking won't be affected. For example, if John's score is 51 and is ranked as 21st; the rank remains the same EVEN if we have all the scores in the RangeForScore. Isn't it?

I believe the issue with this solution is that the RangeForScore is used to generate the rank so even if the failed students are not displayed, their ranks are still calculated which does not produce the desired results.

 

[Namestaken]

Although the RANK function ranks every score (including those failing scores), the ranking won't be affected. For example, if John's score is 51 and is ranked as 21st; the rank remains the same EVEN if we have all the scores in the RangeForScore. Isn't it?

I feel silly, you are absolutely right. The lower scores would simply be ranked higher while the passing grades would be ranked properly. Perfect solution

 

[mfexcel]

I do silly thing a lot. That's the why I learn and grow.

I feel silly, you are absolutely right. The lower scores would simply be ranked higher while the passing grades would be ranked properly. Perfect solution

 

[sooshil]

Great!!! Thank you to both of you..... :D