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 cells 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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"cardingtr" <[email protected]> wrote in
message news:[email protected]...
>
> 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).
>
>
> --
> cardingtr
> ------------------------------------------------------------------------
> cardingtr's Profile:
http://www.excelforum.com/member.php...o&userid=27027
> View this thread: http://www.excelforum.com/showthread...hreadid=465810
>

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

Hi cardingtr

Try this >

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

[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 this 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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--