difference between logical AND and multiplication?

[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

 

[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

 

[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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

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

 

[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

 

[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

 

[Harlan Grove]

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

....

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.