Home 
Search 
Today's Posts 
#1




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 IFfunction. 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 
#2




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 IFfunction. 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 
#3




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 IFfunction. 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 
#4




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 formatConditional 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 IFfunction. 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 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
blank cell turns to 0  New Users to Excel  
Look for change next blank cell in Range  Excel Worksheet Functions  
Text wider than one cell is not displayed in the next empty cell  Excel Discussion (Misc queries)  
How do I make a cell equal to another cells value and not it's fo.  Excel Worksheet Functions  
make a cell empty based on condition  Charts and Charting in Excel 