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!

 

[Legacy 146544]

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

I'm getting more and more aware of this fact.

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.

Never really thought it through like this, thanx!

The above concept though undoubtedly useful (and certainly something you should know)

Knowing the foundations and basics of things, in excel for example, is usefull in being able to use the full scale of possibilities of excel (for example). It let's you be more creative!

Regards

 

[digitz]

Hmm,

I'm a bit confused.
I'm using Excel 2010 on Windows 7

I have:
M2: Value entered by user
N2: Value entered by user
Both M2 and N2 are initially blank until the user enters the data inside them.

Here in O2 I have:
O2=IF(ISBLANK(N2),M2,M2-(N2*M2))
So if N2 is blank it returns M2
But what if M2 is blank? In this case it returns 0, but I want the cell to be blank when M2 has not yet been entered.

What should I do?

 

[jasonb75]

You need the formula result as "" to return a blank, referencing a cell will return 0 if the source is blank.

Try

=IF(M2*N2,M2-(N2*M2),IF(M2,M2,""))

 

[digitz]

You need the formula result as "" to return a blank, referencing a cell will return 0 if the source is blank.

Try

=IF(M2*N2,M2-(N2*M2),IF(M2,M2,""))

I don't quite understand the logic behind this. Can you explain please?

 

[jasonb75]

=IF(M2*N2 will only return a non 0 value if both m2 and n2 contain values, non 0 results evaluate as true and return M2-(N2*M2) while 0 results evaluate as false and return IF(M2, again only a non 0 evaluates as true returning M2 while 0 evaluates false and returns "".

In hindsight this will cause problems if 0 is a valid entry in either of the cells.<!-- / message -->

 

[digitz]

Thanks,
The formula you proposed still gives an error.

I thought of this, but still gives me a 0:

=IF(ISBLANK(N2),M2,IF(ISBLANK(M2),"",M2-(N2*M2)))

 

[digitz]

Sorry, It's working now!
Thank you very much.

 

[digitz]

How about this:
=IF(ISBLANK(N2),"",(M2*N2/2))

If N2 is blank, blank, else M2*N2/2
Now at the same time, if M2 is not blank, display 0

 

[jasonb75]

Not sure I fully understand your logic, are you saying to ignore the N2 blank if M2 is not blank?

What is the desired output for each of the 4 possible results?

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 width=64>M2</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=64>N2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>blank</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">blank</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>not blank</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">blank</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>blank</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">not blank</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>not blank</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">not blank</TD></TR></TBODY></TABLE>

 

[digitz]

How about this:
Q2=IF(ISBLANK(N2),"",(M2*N2/2))

If N2 is blank, blank, else M2*N2/2
Now at the same time, if M2 is not blank, display 0

Here's how it's supposed to be:

In any condition, If N2 is blank, Q2 is blank, else Q2=M2*N2/2
If M2 is blank, in any condition whether N2 is blank or not, Q2 is blank