| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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 |
|
#2
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
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 |
|
#4
|
|||
|
|||
|
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 |
|
#5
|
|||
|
|||
|
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 |
|
#6
|
|||
|
|||
|
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 |
|
#7
|
|||
|
|||
|
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 -- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I change a cell range with a reference cell? | Excel Discussion (Misc queries) | |||
| How do I refer a Range to a Cell | Excel Worksheet Functions | |||
| Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
| Reveal cell formats and extendable range in tool/statusbar/icon. | Excel Worksheet Functions | |||
| How can I assign a range starting cell based on a variable locati. | Excel Worksheet Functions | |||
Linear Mode
