null value VS empty cell VS "" VS 0 VS blank cell

[Legacy 146544]

Hi excellers!

Many times when working with excel, I find myself in trouble when working with empty cells. I ask myself questions like; when is a cell empty or blank? When does it have null value? Does excel evaluate those as the same? And so on!

A couple concrete questions:
If in cell A2 is the following formula:
=IF(A1=4,"","cell A1 is not 4")
When A1 is not 4, is cell A2 empty? Or is it filled with an empty text string?

And if in cell A3 is the formula:
=IF(ISBLANK(A2),"A2 is blank","A2 is not blank")
Can A3 ever show the else condition? Since cell A2 is filled with a formula, can it ever be blank?

I wonder if you guys have some usefull working logics on this,

Cheers!

 

[DonkeyOte]

First question - it is Null. A formula null is a 0 length text string.

Second question - no - not if A2 contains the prior formula.

ISBLANK treats a formula Null as a non-blank (as does COUNTA) - COUNTBLANK however does treat Null as a Blank.

=IF(COUNTBLANK(A2),"A2 is blank","A2 is not blank")

 

[jasonb75]

Another way I've found to work with this problem

=IF(LEN(A2),"A2 is not blank","A2 is blank")

 

[DonkeyOte]

Yes that too - COUNTBLANK is perhaps the more common approach given it can be extended to work across a range of cells.

Which you use is of course entirely dependent upon context.

 

[Legacy 146544]

Nice guys.

So to use the len formula to check if the value of a cell is null one would use:
=IF(LEN(A2)=0,"A2 is null","A2 is not null")
?

Would this work on a cell that is formatted as number, or date value?

Correct me if I'm wrong:
"" = null value = len()=0
?

And:
a cell being blank always gives a null value, but a cell which gives a null value, does not have to be blank
?

Thanks in advance!

<!-- / message -->

 

[jasonb75]

It will work on anything that displays content in a cell, but one thing to be aware of with any formula to check blanks, stray spaces are not blanks.

"" = len 0, " " = len 1

Also if you notice =IF(LEN(A2),"A2 is not blank","A2 is blank")

no =0 included, remember basic logic TRUE = 1 FALSE = 0, so a 0 value returns false by default, although your version is correct if you wanted to reverse the results.

 

[DonkeyOte]

And:
a cell being blank always gives a null value, but a cell which gives a null value, does not have to be blank ?

A Blank and a Null are two very different things.

A Blank cell is devoid of all content. A cell containing a Null is not (it contains a length text string).

A Null might appear to be blank (visually), but so would a cell containing only the space character, however, neither are physically blank.

If you link to a true blank the cell will return 0, eg:

=A1 --> 0 if A1 is completely empty.

 

[Legacy 146544]

@DonkeyOte: I've worked some magic on my sheets the last hour with your info. thx a lot!


@jasonB75:
won't LEN return a number?
From the web:
(In Excel, the Len function returns the length of the specified string)

So using LEN(A2) will give the length of the string in A2... to use this formula in a conditionally (like in an IF function) don't you have to evaluate this number value to something? =0, =10 or whatever you want to evaluate it against.

 

[jasonb75]

@jasonB75:
won't LEN return a number?
From the web:
(In Excel, the Len function returns the length of the specified string)

So using LEN(A2) will give the length of the string in A2... to use this formula in a conditionally (like in an IF function) don't you have to evaluate this number value to something? =0, =10 or whatever you want to evaluate it against.

There are many ways to achieve the same results with excel.

The IF function returns TRUE without comparing results on any non zero numeric value, and FALSE on any zero or empty (not formula blank) cell.

Meaning any nonblank cell would have a LEN>0 and return TRUE.

 

[DonkeyOte]

Just reiterating jasonb75's post - in Excel only the value 0 is FALSE any other value is TRUE, eg:

=IF(0.00001,TRUE,FALSE)

will return TRUE, only

=IF(0,TRUE,FALSE)

will return FALSE

so where your test returns a Number and that number in itself is acting as a test where "if it is anything but 0 do this" you can rely on the above to ensure the appropriate action is undertaken in the IF.

Golden Rule: in cases such as this you should adopt whichever approach makes most sense to you given you maintain the model.

The above concept though undoubtedly useful (and certainly something you should know) is not going to make masses of difference in terms of performance.
The additional >0 test won't really slow your calculations and you may find it is easier for yourself and/or others to audit going forward.

(FWIW: I used the same logic as jasonb75 in my COUNTBLANK example also and I confess I tend to adopt the practice myself - for good or bad).