If and OR

[yichuansancun]

I have the following formula:

=IF(OR($B$38=0,ISERROR($B$38)),"not available",$B$38)

When B38 = 0, it returns "not available"
but when B38 = "#DIV/0" or any other error message, it doesn't return "not available" but the error symbol itself.

Why is that?

How can I make the cell to return "not available" when either of the conditions met?

Thanks,
Perri

 

[Marcelo Branco]

Try this

=IF(ISERROR($B$38),"not available",IF($B$38=0,"not available",$B$38))

M.

 

[Michael M]

Try

=IF(ISERROR($B$38),"not available",IF($B$38=0,"not available",$B$38))

 

[yichuansancun]

Sorry... it was more complicated then I stated early:

Here is the formula that does NOT work:

=IF(OR(AND(Neighborhood<>"",Furnishing<>"",C24<>"",Bedrms<>"",ISERROR($B$38)),$B$38=0),”not available”,(IF(ISERROR($B$38),"please select",$B$38)))

What I try to achieve:

If B38 is error and all the selections (Neighborhood, furnishing, C24, bedrms) are not empty, OR if B38=0, then the result should be "not available"

however,

if B38 is error and one of the selections is empty, the result should be "please select"

Does it make sense?

Thanks in advance!

 

[yichuansancun]

In addition, when I tested the OR statement by F9, it gives me "true", so I think the OR portion is correct, but it doesn't work....

 

[Marcelo Branco]

Maybe this

=IF($B$38=0,"not available",IF(ISERROR($B$38), IF(AND(Neighborhood<>"",Furnishing<>"",C24<>"",Bedrms<>""),"not available","please select"),$B$38))

M.

 

[yichuansancun]

Your formula doesn't work

I solved the problem by breaking the formulas into the following three steps:

the value in C38 determines all the return value

Step 1
C37 = IF(AND(Neighborhood<>"",Furnishing<>"",C24<>"",Bedrms<>"",ISERROR(B38)),"not available",B38)

then goes to Step 2
C36 = F(C37=0,"not available",C37)

then goes to Step 3
=IF(ISERROR(C36),"please select",C36*12)

The value in Step 3 is my final result. It worked but it sucks that I cannot combine the three steps into 1

any more thoughts?

Thanks!

 

[Marcelo Branco]

Sorry my formulas was wrong

This should work

=IF(ISERROR($B$38), IF(AND(Neighborhood<>"",Furnishing<>"",C24<>"",Bedrms<>""),"not available","please select"),IF($B$38=0,0,$B$38))

M.

 

[yichuansancun]

it worked except when B38 = 0, it should return "not available", so I altered the formula to :

=IF(ISERROR($B$38), IF(AND(Neighborhood<>"",Furnishing<>"",C24<>"",Bedrms<>""),"not available","please select"),IF($B$38=0,"not available",$B$38))

Thanks much for you help!!!!

Perri

 

[Marcelo Branco]

it worked except when B38 = 0, it should return "not available", so I altered the formula to :

=IF(ISERROR($B$38), IF(AND(Neighborhood<>"",Furnishing<>"",C24<>"",Bedrms<>""),"not available","please select"),IF($B$38=0,"not available",$B$38))

Thanks much for you help!!!!

Perri

Hi Perri,

Yes... B38 = 0 ---> not available

Good that you fixed it!

Glad to help

M.