Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

excel

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?

like image 552
kyle Avatar asked Mar 16 '10 06:03

kyle


People also ask

How do you return a blank cell if a reference cell is blank?

The ISBLANK function takes a single argument - the cell reference - and returns TRUE if the cell is blank and FALSE if it isn't. For example, the formula =ISBLANK(A1) will return TRUE if A1 is blank and FALSE if it isn't. If you want to return a different value when a cell is blank, you can use the IF function.

How do you return a value if a cell is blank in Excel?

Sometimes you need to check if a cell is blank, generally because you might not want a formula to display a result without input. In this case we're using IF with the ISBLANK function: =IF(ISBLANK(D2),"Blank","Not Blank")

When a formula references a blank cell?

If you have a formula in a worksheet, and the cell referenced by the formula is blank, then the formula still returns a zero value. For instance, if you have the formula =A3, then the formula returns the contents of cell A3, unless cell A3 is blank. In that case, the formula returns a value of zero.

How to return a value if any cell is blank in Excel?

For returning a value if any cell of the Delivery Date column is blank you can use the IF function and the COUNTIF function. COUNTIF (D5,””) will return the number of blank cells and if it finds a blank cell in cell D5 of the Delivery Date column then the number will be greater than 0 and so it will return TRUE otherwise FALSE.

How to distinguish between empty and blank cells in Excel?

Use ISBLANK(A1) to distinguish these situations. ISBLANK is a misnomer: it returns TRUE only if the cell is empty, not if it (also) appears blank.

How do I return an empty string instead of a blank?

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.

Can a cell be empty but its value is a string?

Also, a cell can appear empty, but its value is actually the null string. That can arise by using copy-and-paste-value from a cell whose value is the null string.


1 Answers

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.

like image 81
paxdiablo Avatar answered Sep 23 '22 22:09

paxdiablo