Ranking range of cell with value

[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)

 

[Max]

Try instead in say, C3: =IF(B3="","",RANK(B3,$B$3:$B$12,1))
Copy down to C12

 

[Paul Sheppard]

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).

Hi cardingtr

Try this >

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

 

[cardingtr]

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

 

[Paul Sheppard]

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

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)

 

[cardingtr]

Worked for me!!
Thanks.

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))

 

[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