How to apply a logical OR to an array in Excel
arrays, excel
Solution
8 years later...
=BITOR(A1:A3>0,B1:B3>0) for a 0 or 1 binary response
or
=BITOR(A1:A3>0,B1:B3>0)=1 for a FALSE or TRUE boolean response
Remember CTRL+SHIFT+ENTER!
It seems AND, OR and XOR is not array friendly but BITAND, BITOR and NOT is fine. To do an array friendly XOR you need to break it into other operators e.g.
=BITAND(BITOR(A1:A3,B1:B3),BITOR(NOT(A1:A3),NOT(B1:B3)))=1
Problem
I'm trying to have Excel make me an array by ORing two arrays. As an example, let two arrays `A1:A3` be `7`, `8`, `-3` and `B1:B3` be `4`, `-8`, `-8`. This should be fairly simple but it appears that ``` OR ( A1:A3 > 0 ; B1:B3 > 0 ) ``` returns `TRUE` instead of the array I'm expecting `(TRUE, TRUE, FALSE)`. Of course I could use a dirty trick like ``` (((A1:A3 > 0)*1 + (B1:B3 > 0)*1) >= 1) *1 ``` but who would want to maintain that? Also, there is probably something to search around "CSE (Ctrl+Shift+Enter) Formulas" (http://www.mrexcel.com/articles/CSE-array-formulas-excel.php) but it really looks like black magic. Did I miss something easy? For those curious about it, the actual formula I'm trying to build is a little more complex of course. It's trying to count (via `SUMPRODUCT`) all rows where `(status == A OR status == B) AND Date = some cell`. The OR I'm looking for is just one array of my `SUMPRODUCT` and is not defined in cells (that would be too easy).