Blank (empty) cell always equal to 0??
Is it possible to have Excel read an empty cell as "no value" rather than the value=0? I have a graph and corresponding range with values and somtimes cells in this range should be read as a "blank" cell! Resulting in *-no point-* in the graph. Assume that I have, in cell C5, the following function: IF(AND(ISNUMBER($A5);ISNUMBER($B$4));$A5*$B$4;" "). If, in cell A5, I have "-" (a dash) = no value (no reading), this will result in C5 being a Blank cell, due to the " " in the IF-function. C5 is copied and pasted into the range connected to my graph. I want it to result in no point in the graph! But the graph is reading this cell as if the value was 0.0! Is there a way to avoid this?? Your help is very much appreciated. -- ulfah ------------------------------------------------------------------------ ulfah's Profile: http://www.excelforum.com/member.php...o&userid=29204 View this thread: http://www.excelforum.com/showthread...hreadid=507215 |
Blank (empty) cell always equal to 0??
I'd add another column and use a formula like:
=if(b2="",na(),b2) then use that column in the chart. ulfah wrote: Is it possible to have Excel read an empty cell as "no value" rather than the value=0? I have a graph and corresponding range with values and somtimes cells in this range should be read as a "blank" cell! Resulting in *-no point-* in the graph. Assume that I have, in cell C5, the following function: IF(AND(ISNUMBER($A5);ISNUMBER($B$4));$A5*$B$4;" "). If, in cell A5, I have "-" (a dash) = no value (no reading), this will result in C5 being a Blank cell, due to the " " in the IF-function. C5 is copied and pasted into the range connected to my graph. I want it to result in no point in the graph! But the graph is reading this cell as if the value was 0.0! Is there a way to avoid this?? Your help is very much appreciated. -- ulfah ------------------------------------------------------------------------ ulfah's Profile: http://www.excelforum.com/member.php...o&userid=29204 View this thread: http://www.excelforum.com/showthread...hreadid=507215 -- Dave Peterson |
Blank (empty) cell always equal to 0??
why add another column, when you can just replace " " with NA()?
"Dave Peterson" wrote: I'd add another column and use a formula like: =if(b2="",na(),b2) then use that column in the chart. ulfah wrote: Is it possible to have Excel read an empty cell as "no value" rather than the value=0? I have a graph and corresponding range with values and somtimes cells in this range should be read as a "blank" cell! Resulting in *-no point-* in the graph. Assume that I have, in cell C5, the following function: IF(AND(ISNUMBER($A5);ISNUMBER($B$4));$A5*$B$4;" "). If, in cell A5, I have "-" (a dash) = no value (no reading), this will result in C5 being a Blank cell, due to the " " in the IF-function. C5 is copied and pasted into the range connected to my graph. I want it to result in no point in the graph! But the graph is reading this cell as if the value was 0.0! Is there a way to avoid this?? Your help is very much appreciated. -- ulfah ------------------------------------------------------------------------ ulfah's Profile: http://www.excelforum.com/member.php...o&userid=29204 View this thread: http://www.excelforum.com/showthread...hreadid=507215 -- Dave Peterson |
Blank (empty) cell always equal to 0??
Sometimes, the look of the data is important and #n/a's are pretty ugly. And
sometimes those values could be used in other formulas and errors could mess up those subsequent formulas. They could be hidden with format|Conditional formatting, though. And other formulas could be rewritten to avoid the errors. (I'd just hide the whole helper column after I made the chart.) But your post does show a problem with my formula. I didn't notice that the OP used " " (I always use "" (no space)). And the OP uses a semicolon instead. I think that this formula is better: =if(trim(b2)="";na();b2) But I'd actually change the original formula: =IF(AND(ISNUMBER($A5);ISNUMBER($B$4));$A5*$B$4;"") Those spaces are can be a problem. Sloth wrote: why add another column, when you can just replace " " with NA()? "Dave Peterson" wrote: I'd add another column and use a formula like: =if(b2="",na(),b2) then use that column in the chart. ulfah wrote: Is it possible to have Excel read an empty cell as "no value" rather than the value=0? I have a graph and corresponding range with values and somtimes cells in this range should be read as a "blank" cell! Resulting in *-no point-* in the graph. Assume that I have, in cell C5, the following function: IF(AND(ISNUMBER($A5);ISNUMBER($B$4));$A5*$B$4;" "). If, in cell A5, I have "-" (a dash) = no value (no reading), this will result in C5 being a Blank cell, due to the " " in the IF-function. C5 is copied and pasted into the range connected to my graph. I want it to result in no point in the graph! But the graph is reading this cell as if the value was 0.0! Is there a way to avoid this?? Your help is very much appreciated. -- ulfah ------------------------------------------------------------------------ ulfah's Profile: http://www.excelforum.com/member.php...o&userid=29204 View this thread: http://www.excelforum.com/showthread...hreadid=507215 -- Dave Peterson -- Dave Peterson |
All times are GMT +1. The time now is 11:56 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com