Logical functions AND,OR in array formulas work ?

[Tom]

Assuming that in A2:C5 is a table of data,
Why is it so that these two worksheet array formulas give different
results:
1) {=SUM(IF(OR(($A$2:$A$5=value1);($B$2:$B$5=value2));$C$2:$C$5;""))}
2) {=SUM(IF(($A$2:$A$5=value1)+($B$2:$B$5=value2);$C$2:$C$5;""))}
and only the second one gets correct result (summing C2:C5 only if
A2:A5 has the value of value1 or B2:B5 has the value of value2)
I'm not sure but it looks like the logical function OR in the array
form
does not translate to na array {t,f,f,t....} but to a single {t|f}
value, but
(($A$2:$A$5=value1)+($B$2:$B$5=value2)) translates to an array of
{t,f,f,t...}. Am I right ? Why is it so ?

Thanks
Tom

 

[Carim]

Hi Tom,

As a general rule, in array formulas :
AND = *
OR = +

HTH

 

[Mike H]

Tom,

They're both giving an incorrect result.

Highlight this part of your first formula
OR(($A$2:$A$5=1),($B$2:$B$5=4))
tap F9 and you'll see it evaluates as a single TRUE so everything in column
C is summed

No2 sums column C every time value 1 is in Col A and value 2 is in column B
change the + to a * to correct this

why not use the much simpler and non array
=SUMPRODUCT((A2:A5=1)*(B2:B5=4)*(C2:C5))

Mike

 

[Lazzzx]

Hi,

Lets assume that you have the folowing values in first five rows of columns
A and B.
Col A;Col B
value1;value2
value3;value2
value1;value3
value3;value3

Then lets evaluate the following arrayformulas.
{=OR($A$2:$A$5=value1)} equals {=OR({t,f,t,f})}
resulting in {t}

{=OR($A$2:$A$5=value1)} equals {=OR({t,t,f,f})}
resulting in {t}

then
{=OR(($A$2:$A$5=value1);($B$2:$B$5=value2))}
equals
{=OR({t,f,t,f};{t,t,f,f})}
equals
{=OR({t};{t})}
resulting in {t}

hence,
{=IF(OR(($A$2:$A$5=value1);($B$2:$B$5=value2));$C$2:$C$5;"")}
will always be true if there is at least one value1 in column A, or at least
one value2 in
column B. If there is no value1 in column A, and no Value2 in columnB the
resulting array from
the "IF" function will be {"","","",""}, and =SUM({"","","",""}) results in
#VALUE.

rgds,
Lazzzx

 

[Tom]

Following Carim's suggestion I rewrote my array formulas to include */
+ in place of and/or and now they are working fine. Thanks too for
that sumproduct function and that trick of highlighting part of your
formula and evaluating it with F9 - it's very useful.
It seems that the mistake I made was about thinking that {OR{t,f,t,f},
{t,t,f,f}} evaluates to an array of {t,t,t,f} and not to the simple
{t}

Tom