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

User · Answer

Beware  There are also cells which are seemingly blank  but are not truly empty but containg  quot  quot  or something that is called NULL in other languages  As an example  when a formula results in  quot  quot  or such result is copied to a cell  the formula ISBLANK A1    returns FALSE  That means the cell is not truly empty  The way to go there is to use enter code here COUNTBLANK A1   Which finds both truly empty cells and those containing  quot  quot   See also this very good answer here

User · Answer

You may have to use  IF ISNUMBER A1  A1 1  in some situations where you are looking for number values in cell

User · Answer

Compare the cell with     empty line     IF A1    1 0

User · Answer

Paxdiablo s answer is absolutely correct   To avoid writing the return value 1 twice  I would use this instead    IF OR ISBLANK A1  TRIM A1      1 0

User · Answer

If you ve got a cell filled with spaces or blanks  you can use    Len Trim A2     0   if the cell you were testing was A2

User · Answer

if a1     1   0     In this formula if the cell is empty then the result would be 1 else it would be 0

User · Answer

Since required quite often it might as well be brief    1  A1       This will not return 1 if the cell appears empty but contains say a space or a formula of the kind  IF B1 3  Yes      where B1 does not contain 3    A1    will return either TRUE or FALSE but those in an equation are treated as 1 and 0 respectively so multiplying TRUE by 1 returns 1   Much the same can be achieved with the double unary --    -- A1         where when A1 is empty one minus negates TRUE into -1 and the other negates that to 1  just   in place of -- however does not change TRUE to 1

User · Answer

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    lt -A- gt   lt -B- gt   lt -C- gt          0   TRUE         1   TRUE         2   TRUE         3   TRUE         4   TRUE         5   TRUE   a     1   FALSE  lt -A- gt   lt -B- gt   lt -C- gt    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

User · Answer

P4 is the cell I test for    IF ISBLANK P4  1 0

[excel] Return a value of '1' a referenced cell is empty

Examples related to excel