IF statement: how to leave cell blank if condition

Question

I would like to write an IF statement, where the cell is left blank if the condition is FALSE. Note that, if the following formula is entered in C1 (for which the condition is false) for example:

 =IF(A1=1,B1,"")

and if C1 is tested for being blank or not using =ISBLANK(C1), this would return FALSE, even if C1 seems to be blank. This means that the =IF(A1=1,B1,"") formula does not technically leave the cells blank if the condition is not met.

Any thoughts as to a way of achieving that? Thanks,

Answer 1

I wanted to add that there is another possibility - to use the function na().

e.g. =if(a2 = 5,"good",na());

This will fill the cell with #N/A and if you chart the column, the data won't be graphed. I know it isn't "blank" as such, but it's another possibility if you have blank strings in your data and "" is a valid option.

Also, count(a:a) will not count cells which have been set to n/a by doing this.

Answer 2

Unfortunately, there is no formula way to result in a truly blank cell, "" is the best formulas can offer.

I dislike ISBLANK because it will not see cells that only have "" as blanks. Instead I prefer COUNTBLANK, which will count "" as blank, so basically =COUNTBLANK(C1)>0 means that C1 is blank or has "".

If you need to remove blank cells in a column, I would recommend filtering on the column for blanks, then selecting the resulting cells and pressing Del. After which you can remove the filter.

Answer 3

I've found this workaround seems to do the trick:

Modify your original formula:

=IF(A1=1,B1,"filler")

Then select the column, search and replace "filler" with nothing. The cells you want to be blank/empty are actually empty and if you test with "ISBLANK" it will return TRUE. Not the most elegant, but it's quick and it works.

Answer 4

The formula in C1

=IF(A1=1,B1,"")

is either giving an answer of "" (which isn't treated as blank) or the contents of B1.

If you want the formula in D1 to show TRUE if C1 is "" and FALSE if C1 has something else in then use the formula

=IF(C2="",TRUE,FALSE)

instead of ISBLANK

Answer 5

To Validate data in column A for Blanks

Step 1: Step 1: B1=isblank(A1)

Step 2: Drag the formula for the entire column say B1:B100; This returns Ture or False from B1 to B100 depending on the data in column A

Step 3: CTRL+A (Selct all), CTRL+C (Copy All) , CRTL+V (Paste all as values)

Step4: Ctrl+F ; Find and replace function Find "False", Replace "leave this blank field" ; Find and Replace ALL

There you go Dude!

Answer 6

You can do something like this to show blank space:

=IF(AND((E2-D2)>0)=TRUE,E2-D2," ")

Inside if before first comma is condition then result and return value if true and last in value as blank if condition is false