AND logical operator

[Catherine Brown]

Hi All,

I have a worksheet which was inherrited from somewhere,
so I am unable to contact the orginal author.

When using the AND logical function they have not used it
in the normal way of "AND(expression1,expression2)"
Instead they have used it as follows "(expression1)+AND
(expression2)" If I try and remove the "+" sign Excel
then suggests that I need to put in a "*" sign. When I
switch between having these two symbols next the the AND
I get different results.

Could anyone tell me why these have been used and what is
the difference between each. As an aside, when I use the
standard "AND(expression1,expression2)" it seems to
behave like the *AND option.

Any help would be greatly appreciated.

Catherine

 

[Biff]

Hi Catherine,

Depending on what the actual formula does, it appears as
though the author was using +AND to add 1 to the
condition. The '+' and '*' are just math operators. For
example:

=5+AND(A1) = 6 if A1 contains any numeric value.
=5*AND(A1) = 5 if A1 contains any numeric value

If A1 is a non-numeric or blank the formula returns
#VALUE. Without seeing the actual formula it's hard to
tell why it was written that way.

Biff

 

[Catherine Brown]

Thanks for the help.

Not to confuse the issue further, here is the formula
that I am using. All values involved are a time of day.

=IF((G7="")+AND(G17=""),0,IF(ISTEXT(G7),G23-G24,IF(G23-
G24>X27,X27,G23-G24)))

In the case of the above example as written (first
section), if there is no value in G7, but there is
however a calculated value in G17 I am coming up with the
result of 0. However if I change it to *AND I receive a
correctly calculated result. But then the formula will
still calculate out when there is nothing in G7 and no
result for the formula in G17.

Any ideas??

 

[Leo Heuser]

Hi Catherine

AND is not necessary here.
Actually AND is only neccesary with compound
statements (except if used as a bitwise operator,
which is only possible in VBA)

The statement (G17="") is either TRUE or FALSE
If it is TRUE, the value of the parenthesis is 1 (one)
since TRUE is considered 1 in calculations.
If (G17="") is FALSE, then the value of the parenthesis is
0 (zero), since FALSE is considered 0 in calculations.

AND(G17="") is TRUE, if the parenthesis is TRUE
and FALSE if the parenthesis is FALSE, so there is
no need to use AND in this situation.

So instead of (G7="")+AND(G17=""), we have
(G7="")+(G17="")

The 4 possible outcomes are
0 + 0 = 0
0 + 1 = 1
1 + 0 = 1
1 + 1 = 1
Which is the truth table for the OR operator,
so instead of (G7="")+(G17=""), we could use
OR(G7="",G17="")
That's why "if there is no value in G7 [(G7="") is TRUE],
but there is however a calculated value in G17 [(G17="") is FALSE]
I am coming up with the result of 0" (outcome #3)

If you use the * operator like this
(G7="")*(G17="")

the 4 outcomes are

0 * 0 = 0
0 * 1 = 0
1 * 0 = 0
1 * 1 = 1

Which is the truth table for the AND operator, and here
outcome #3 is FALSE (0), hence the calculated result is returned.

So in short:

(G7="")+(G17="") means OR(G7="",G17="")

and

(G7="")*(G17="") means AND(G7="",G17="")

I hope, this explanation shed some light on the matter

--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.

 

[Catherine Brown]

Thankyou very much for your help it makes much more sense
to me now.

Catherine

-----Original Message-----
Hi Catherine

AND is not necessary here.
Actually AND is only neccesary with compound
statements (except if used as a bitwise operator,
which is only possible in VBA)

The statement (G17="") is either TRUE or FALSE
If it is TRUE, the value of the parenthesis is 1 (one)
since TRUE is considered 1 in calculations.
If (G17="") is FALSE, then the value of the parenthesis is
0 (zero), since FALSE is considered 0 in calculations.

AND(G17="") is TRUE, if the parenthesis is TRUE
and FALSE if the parenthesis is FALSE, so there is
no need to use AND in this situation.

So instead of (G7="")+AND(G17=""), we have
(G7="")+(G17="")

The 4 possible outcomes are
0 + 0 = 0
0 + 1 = 1
1 + 0 = 1
1 + 1 = 1
Which is the truth table for the OR operator,
so instead of (G7="")+(G17=""), we could use
OR(G7="",G17="")
That's why "if there is no value in G7 [(G7="") is TRUE],
but there is however a calculated value in G17 [(G17="") is FALSE]
I am coming up with the result of 0" (outcome #3)

If you use the * operator like this
(G7="")*(G17="")

the 4 outcomes are

0 * 0 = 0
0 * 1 = 0
1 * 0 = 0
1 * 1 = 1

Which is the truth table for the AND operator, and here
outcome #3 is FALSE (0), hence the calculated result is returned.

So in short:

(G7="")+(G17="") means OR(G7="",G17="")

and

(G7="")*(G17="") means AND(G7="",G17="")

I hope, this explanation shed some light on the matter :- )

--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.

"Catherine Brown" <[email protected]> skrev i en meddelelse

Thanks for the help.

Not to confuse the issue further, here is the formula
that I am using. All values involved are a time of day.

=IF((G7="")+AND(G17=""),0,IF(ISTEXT(G7),G23-G24,IF(G23-
G24>X27,X27,G23-G24)))

In the case of the above example as written (first
section), if there is no value in G7, but there is
however a calculated value in G17 I am coming up with the
result of 0. However if I change it to *AND I receive a
correctly calculated result. But then the formula will
still calculate out when there is nothing in G7 and no
result for the formula in G17.

Any ideas??

.

 

[Leo Heuser]

You're welcome, Catherine.
Glad you could use it

LeoH