Ranking range of cell with value

C

cardingtr

How do you rank the range of cells but ignores cells with no value?

I tried =RANK(B3,$B$3:$B$12,1) but it returns an "#N/A" if some cell
is blank in a range (B3 to B12)
 
P

Paul Sheppard

cardingtr said:
How do you rank the range of cells but ignores cells with no value?

I tried =RANK(B3,$B$3:$B$12,1) but it returns an "#N/A" if some cells
is blank in a range (B3 to B12).
Click to expand...

Hi cardingtr

Try this >

=IF(B3="","",RANK(B3,$B$3:$B$12,1))
 
C

cardingtr

It still returns an "#N/A".
How can you make "#N/A" disappear?
The formula does not ignore empty spaces.
 
P

Paul Sheppard

cardingtr said:
It still returns an "#N/A".
How can you make "#N/A" disappear?
The formula does not ignore empty spaces.
Click to expand...

Hi cardingtr

=IF(B3="","",RANK(B3,$B$3:$B$12,1)), worked ok for me, anyway try thi
instead

=IF(ISNA(RANK(B3,$B$3:$B$12,1)),"",RANK(B3,$B$3:$B$12,1)
 
C

cardingtr

Worked for me!!
Thanks.

Paul said:
Hi cardingtr

=IF(B3="","",RANK(B3,$B$3:$B$12,1)), worked ok for me, anyway try this
instead

=IF(ISNA(RANK(B3,$B$3:$B$12,1)),"",RANK(B3,$B$3:$B$12,1))
Click to expand...
 
M

Max

Another option other than directly error-trapping
the #N/A return of the RANK(...) itself:

In C3: =IF(ISNUMBER(B3),RANK(B3,$B$3:$B$12,1),"")
C3 copied down to C12
 
Top