excel definition of IS NULL 1

[SmellyMelly]

I want to create a result of null (not zero) in an IF statement in excel.
This is what I have:

Cell G15
=IF(ISERROR((D2-E2)/E2),"",(D2-E2)/E2)
Cell N15
=IF((G15>=1),1,0)

The problem is if G15 is blank (is null?), cell N15 calculates a 1.

Question: are the symbols "" the correct symbols for IS NULL, or are they reflecting infiniti (which is greater than one and why the second IF statement does not work)?

Thanks for your help!

 

[SkipVought]

Hi,
How about checking of the cell in question is a number...

=IF(ISNUMBER(F2),IF(F2>=1,1,0),0)

Here is a list of the IS Functions...

ISBLANK(value)
ISERR(value)
ISERROR(value)
ISLOGICAL(value)
ISNA(value)
ISNONTEXT(value)
ISNUMBER(value)
ISREF(value)
ISTEXT(value)

Hope this helps Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SmellyMelly]

worked, thank you!

 

[KenWright]

You can also check to see if the length of the cell contents is equal to 0. Note I said the length and not the value:-

=LEN(A1)=0

Regards
Ken.................

 

[Cresby]

I always use "" as if it is a null but there are many situations where I get a 0 when I expected a null error

eg =A1+A2 equates to zero if both are null
so testing for null is paramount.

 

[xlbo]

Technically, you cannot get ISNULL = TRUE in a worksheet
a cell will give TRUE to ISEMPTY but being as there is always a cell there, it can never be null
Also, a formula CANNOT return an empty cell. The least it can return is an empty string which does NOT equate to an empty cell. Hence you must test for "" or len = 0 as has been suggested already Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[TonyJollans]

The original problem here is that cell G15 is not blank (note that Excel does not have any naturally occurring nulls). It contains a formula which returns a (zero length) string and is, therefore, text.

The check in N15 expects G15 to be numeric or empty (in which case Excel treats it as zero).

The given solutions address the problem in various ways and all work. Personally I prefer to ensure that I have numbers if I need them and format them not to display if needs be (custom format "#" usually works).

Enjoy,
Tony