ISBLANK with AND Formula Returning Value Error

[ScottTemple]

Hello,

I have the following formula in cell O2:

=IF(AND(ISBLANK(I2),ISBLANK(H2)),"",((+H2)/(I2^2*3.1415929/4*0.16*12)))

However, when I have a value in H2 and no value in I2 I get a #DIV/0! error, but if I have a value in I2 and no value in H2, I get zero. Any suggestions?

 

[Joe4]

Do you want it to return a blank if EITHER H2 or I2 is blank?
If so, you need to change your AND to OR.

 

[Scott Huish]

Perhaps:
=IF(H2*I2,(H2/(I2^2*PI()/4*0.16*12)),"")

 

[ScottTemple]

Huh, that worked. What's strange is that I have that formula in another document and it works with AND.

=IF(AND(ISBLANK(H2),ISBLANK(I2)),"",(H2+I2))

 

[Joe4]

Huh, that worked. What's strange is that I have that formula in another document and it works with AND.

It all depends on what you are trying to do. It is important to understand the difference between the two.
AND will only return TRUE if each and everything you are checking is TRUE.
OR will return TRUE if any one of the things you are checking is TRUE.

So ALL need to be true for AND, and only one needs to be true for OR.

I also do not like to use ISBLANK, as it will not identify blanks returned by formulas or spaces.
I often use ="" instead, i.e.

H2=""

but also note that a space is NOT the same as a blank.

 

[ScottTemple]

That's seems a bit odd though as in one workbook the AND works and in the other workbook either AND or OR works. All cells are standard Number formatted.

Workbook 1 - Returns a #DIV/0! if AND is used and a Blank if OR is used
=IF(AND(ISBLANK(I2),ISBLANK(H2)),"",((+H2)/(I2^2*3.1415929/4*0.16*12))) Error
=IF(OR(ISBLANK(I2),ISBLANK(H2)),"",((+H2)/(I2^2*3.1415929/4*0.16*12))) Works

Workbook 1 - Returns a Blank if AND or OR is used
=IF(AND(ISBLANK(H2),ISBLANK(I2)),"",(H2+I2)) Works
=IF(OR(ISBLANK(H2),ISBLANK(I2)),"",(H2+I2)) Works

 

[Joe4]

What exactly is in cells H2 and I2?
As long as H2 and I2 have numeric values, you won't get an error in any of them.
The situation where you would get an answer is if I2 is not a non-zero number. That would cause the AND formula to return an error (if I2 is a zero, space, or non-numeric entry).

 

[ScottTemple]

What exactly is in cells H2 and I2?
As long as H2 and I2 have numeric values, you won't get an error in any of them.
The situation where you would get an answer is if I2 is not a non-zero number. That would cause the AND formula to return an error (if I2 is a zero, space, or non-numeric entry).

H2 has the following formula, that returns a number value. G2 contains a manually inputted number (e.g. 309).
=IF(G2="","",(G2))

I2 contains a manually inputted number (e.g. 0.21)

That's with the ISBLANK formula =IF(OR(ISBLANK(I2),ISBLANK(H2)),"",((+H2)/(I2^2*3.1415929/4*0.16*12)))

 

[Joe4]

H2 has the following formula, that returns a number value. G2 contains a manually inputted number (e.g. 309).
=IF(G2="","",(G2))

There is your problem right there.
If that is the formula in cell H2, you cannot use:

ISBLANK(H2)

as that formula will ALWAYS return FALSE, no matter what the formula in cell H2 returns, as "" is NOT the same as blank.

Change this part of your formula:

ISBLANK(H2)

to

=H2=""

 

[ScottTemple]

Not sure how to write that

=IF(OR(ISBLANK(I2),=H2="","",((+H2)/(I2^2*3.1415929/4*0.16*12)))