Forum Discussion
IF FALSE THEN BLANK
What seems to work for me is to put a blank space in the code for the "false" result. For example, if I want the target cell to show * if cell B249 is "X", and an empty cell if cell B249 is not "X", my syntax is =IF(B249="X","*"," ") Notice the single space between the last two quotes. You of course get an "empty cell" warning, but presumably that's OK, because you wanted it to be empty.
Returning "" or " " leaves an apparently blank cell, but if it is in a chart, it plots as Zero, even if "hidden and empty cells - plot as gap" is set. If you take an average of data including these cells, they are not included, as if the cells are blank or empty (they are not treated as Zero values). You can change the formula to return NA() and set the chart to "show #NA as gap" and it looks good, but the average now reports #NA.
Is there a way to get Excel to treat these cells consistently as "blank"? Is there any benefit to the way it behaves now (i.e. some reason not to change it)?
