Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Logical functions AND,OR in array formulas work ?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Logical functions AND,OR in array formulas work ?
Hi Tom,
As a general rule, in array formulas : AND = * OR = + HTH |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Logical functions AND,OR in array formulas work ?
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 "Tom" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Logical functions AND,OR in array formulas work ?
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" skrev i meddelelsen ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Logical functions AND,OR in array formulas work ?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Formulas for logical functions | Excel Discussion (Misc queries) | |||
Formulas assignment from array to range in VSTO Excel doesn't work | Excel Worksheet Functions | |||
Can array formulas work if rows are inserted? | Excel Worksheet Functions | |||
how array functions work mmult,minverse etc | Excel Worksheet Functions | |||
Work around for limit on entering array formulas? | Excel Programming |