Ranking range of cell with value
 

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

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" wrote in
message ...

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

cardingtr Wrote:
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))


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=465810

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


Paul Sheppard Wrote:
Hi cardingtr

Try this

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


--
cardingtr
------------------------------------------------------------------------
cardingtr's Profile: http://www.excelforum.com/member.php...o&userid=27027
View this thread: http://www.excelforum.com/showthread...hreadid=465810

cardingtr Wrote:
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))


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=465810

Worked for me!!
Thanks.

Paul Sheppard Wrote:
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
------------------------------------------------------------------------
cardingtr's Profile: http://www.excelforum.com/member.php...o&userid=27027
View this thread: http://www.excelforum.com/showthread...hreadid=465810

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