IF, AND

[Ibrahim Awwad]

Dear All,

I'm trying to use theIF function as following =IF(And
(y=0,z=0),x,z), but the -AND- logical test isn't working. The formula part of
the true value is implemented even if the y=0 or z=0 not both of them.

Is there any help.
--
*********
IT Manager
DeLaval Ltd.
Cairo-Egypt
*********
|-----------------------------|
|Islam is peace not Terror|
|-----------------------------|

 

[Springbok]

Hi,
Not sure if the problem lies in the format of the numbers (i.e. text)? Have
you tested it?
Cheers,
Jon

 

[Ibrahim Awwad]

Hi Yes i tested the following exactly:
=IF(AND($A$11=0,B4=0),"",(D4-$A$11)/$A$11)

But now the formula part [(D4-$A$11)/$A$11] works even if only one of the
two conditions [($A$11=0,B4=0)] is true. I think the folrmula shouldn't work
until the both conditions after AND are true.

 

[Bob Phillips]

Also, are y and z named ranges or are you really using cell references?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Your reasoning is correct. Whilst the cells A11 and B4 look empty, are you
sure that they do not contain spaces(s). Do a =LEN(A11) and =LEN(B4)
somewhere, if they are not both 0, that is the problem.

Your formula also has a problem if B4 is not 0, but a11 is, as it returns a
#DIV/0! error (dividing by 0 - A11).

--

HTH

RP
(remove nothere from the email address if mailing direct)

Hi Yes i tested the following exactly:
=IF(AND($A$11=0,B4=0),"",(D4-$A$11)/$A$11)

But now the formula part [(D4-$A$11)/$A$11] works even if only one of the
two conditions [($A$11=0,B4=0)] is true. I think the folrmula shouldn't work
until the both conditions after AND are true.

Hi,
Not sure if the problem lies in the format of the numbers (i.e. text)? Have
you tested it?
Cheers,
Jon

 

[nbrcrunch]

Your formula

=IF(And(y=0,z=0),x,z)

will not work unless you are working with named ranges. (Y & Z are
unsafe named ranges because there is the danger of it being
interpretted as the whole columns of Y or Z.)

Using cell references:

=IF(And(B2=0,C2=0),B2,C2)

In the above, if both B2 and C2 equal zero, then the answer will be
zero. If both are not zero, the answer will be whatever is in cell C2.
However, if B2=2 and C2=0, your answer will be zero, making it appear
that no matter what, your answer is zero.