Return a value of '1' a referenced cell is empty
In Excel, I need to return a value of 1
if a referenced cell is empty
I can do it if the value is zer开发者_开发百科o
but how do I do it if it is empty?
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.
P4 is the cell I test for:
=IF(ISBLANK(P4),1,0)
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)
=if(a1="","1","0")
In this formula if the cell is empty then the result would be 1
else it would be 0
Compare the cell with "" (empty line):
=IF(A1="",1,0)
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
You may have to use =IF(ISNUMBER(A1),A1,1)
in some situations where you are looking for number values in cell.
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
).
Beware:
There are also cells which are seemingly blank
, but are not truly empty but containg "" or something that is called NULL
in other languages. As an example, when a formula results in "" 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 "". See also this very good answer here
精彩评论