difference between logical AND and multiplication?

E

ergo

Hi,

please consider the following example:

a c 1
a d 2

Why is (as matrix formula)

=SUM(IF((A1:A2="a")*(B1:B2="d");C1:C2;0))

(which is 2) different from (as matrix formula)

=SUM(IF(AND(A1:A2="a";B1:B2="d");C1:C2;0))

(which is 0)? I would expect 2 in both cases.

Rregerds Olaf
 
B

Bernard Liengme

For reason known only by the Excel developers, the Boolean operators cannot
be used in array formulas.

ASIDE: Please note "array formulas" not "matrix formulas" in English
best wishes
 
S

Sandy Mann

If you highlight the relevant parts of the formulas and then press f9 you
will see what thatpart of the formula resolves to.

The AND() will only be TRUE if all four cells are TRUE whereas the
multiplications are done individually.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
B

bj

the logical statements are not identical
the first is the same as
(A1="a")*(B1="d")*C1 +(A2="a")*(B2="d")*C2
(1)(0)(1)+(1)(1)(2)=2

the second
(A1="a")*(B1="d*(A2="a")*(B2="d")*C1+(A1="a")*(B1="d*(A2="a")*(B2="d")*C2
(1)(0)(1)(1)(1)+(1)(0)(1)(1)(2)=0
 
K

krcowen

Olaf

Evaluating the second formula a piece at a time (by highlighting the
innermost parts of the formula and evaluating with F9) you can see
that a1:a2="a" evaluates to True, True; then B1:B2="d" evaluates to
False, True and therefore AND evaluates to False; hence the IF formula
evaluates to 0 and then the SUM is zero. In the first formula the
True, True and the False, True are multiplied, giving you a 0,1, which
is further multiplied by the 1,2 in C1:C2 leading to SUM evaluating to
2.

Ken
 
B

Bob Umlas

Also known by me, Bernard! -- if ANY of the items in the AND yield FALSE,
the whole result is FALSE, rendering the usefulness of AND(...) useless in
this kind of formula!
Actually, I know you know that too!
Bob Umlas
 
H

Harlan Grove

Bernard Liengme said:
For reason known only by the Excel developers, the Boolean operators cannot
be used in array formulas.
Click to expand...
....

They're *NOT* operators, they're functions. And there lies yet another
really bad Excel design decision. It's a function that HAS TO return a
SINGLE value. And that's what makes it effectively useless in array
formulas.
 
Top