excel - Return a value of '1' a referenced cell is empty

Question

In Excel, I need to return a value of 1 if a referenced cell is empty

I can do it if the value is zero but how do I do it if it is empty?

Answer 1

You can use:

=IF(ISBLANK(A1),1,0)

but you should be careful what you mean by empty cell. I've been caught out by this before. If you want to know if a cell is truly blank, isblank, as above, will work. Unfortunately, you sometimes also need to know if it just contains no useful data.

The expression:

=IF(ISBLANK(A1),TRUE,(TRIM(A1)=""))

will return true for cells that are either truly blank, or contain nothing but white space.

Here's the results when column A contains varying amounts of spaces, column B contains the length (so you know how many spaces) and column C contains the result of the above expression:

<-A-> <-B-> <-C->
        0   TRUE
        1   TRUE
        2   TRUE
        3   TRUE
        4   TRUE
        5   TRUE
  a     1   FALSE
<-A-> <-B-> <-C->

To return 1 if the cell is blank or white space and 0 otherwise:

=IF(ISBLANK(A1),1,if(TRIM(A1)="",1,0))

will do the trick.

This trick comes in handy when the cell that you're checking is actually the result of an Excel function. Many Excel functions (such as trim) will return an empty string rather than a blank cell.

You can see this in action with a new sheet. Leave cell A1 as-is and set A2 to =trim(a1).

Then set B1 to =isblank(a1) and B2 to isblank(a2). You'll see that the former is true while the latter is false.