Rank

[RussChilds]

Hi All,

I'm using the RANK formula:

=RANK(BL2,$BL$2:$BL$186,0)

Some students do not have a grade so their cell is blank. How do I get the formula to ignore the blank cells?

Many thanks,
Russ

 

[hotabae]

If I'm understanding this correctly, I think you can just replace what you have with:

=IFNA(Rank(BL2,$BL$2:$BL$186,0),"")

This will leave a blank space where the grade is blank.

If you want to leave something else there, you can just add it between the quotes like so:

=IFNA(Rank(BL2,$BL$2:$BL$186,0),"No Grade")​

 

[RussChilds]

Hi hotabae,

Thanks for the reply. Sadly it doesn't work. For example cell BL2 is just empty and does not have a #N/A.

Of the cells BL2 to BL186 only 12 pupils do not have a grade and their cell is empty

Hope that helps?

Russ

 

[Ricksp]

The RANK function ignores strings and empty cells for the purpose of displaying the rank.
Students without grade and with cells marked as empty will not be ranked (and the formula will show #N/A. Check hotabae formula).
However if you typed space instead of leaving the cell empty, students without grade will not be ranked either but the RANK function will show #VALUE !.
In order to eliminate the #VALUE ! the IFNA has to be replaced by IFERROR.

 

[hotabae]

If you're still having trouble after reading through Ricksp's #4 , could you post a sample of your data?

Something like this (how I initially interpreted your data):

[TABLE="width: 192"]
<colgroup><col width="64" style="width:48pt" span="3"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"]A[/TD]
[TD="width: 64, bgcolor: transparent"]B[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]RANK[/TD]
[TD="bgcolor: transparent"]GRADE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]#N/A[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]1[/TD]
[/TR]
</tbody>[/TABLE]

A2 Formula: =RANK(B2,$B$2:$B$6,0)

 

[RussChilds]

Thanks Ricksp & hotabae! Very helpful. All working now. Cheers!