40

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).

Improve this question
1

8 Answers 8

Reset to default
38

You can't typically use OR or AND in "array formulas" because, as you have discovered here, they return only a single result (AND is TRUE only if all conditions are TRUE, OR is TRUE is any are TRUE, as you would expect, so in your example you'll get TRUE as long as at least one of the 6 values is positive).

I'd use something similar to your suggestion, using + in place of OR effectively, although you don't need the first two *1 because the + co-erces, so this would suffice

=((A1:A3 > 0) + (B1:B3 > 0) >0) *1

although if you have a single column which can be one thing or another then that's mutually exclusive, surely, so perhaps that needs just

=(A1:A3="A")+(A1:A3="B")

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thank you for the explanation. Still hoping for a "cleaner" formula though.
Probably depends on your definition of "cleaner"....but I suspect there isn't any more elegant way.....perhaps using IF functions is more transparent, e.g. =IF(A1:A3>0,1,IF(B1:B3>0,1,0),0)......but you'd have to "array enter" that, even within SUMPRODUCT
I found these articles to be helpful: dailydoseofexcel.com/archives/2004/12/04/… and cpearson.com/excel/arrayformulas.aspx
Just verified that within SUMPRODUCT (which @PPC stated was the ultimate goal), you don't even need the multiplication-by-1 trick, the result already comes out as an array of 1's and 0's. This is as clean as it can get...
See stackoverflow.com/questions/63289771/…, which suggests "--" as a prefix operator to convert a logical array into a 0-1 array. The underlying hack is to use a prefix "-" to convert a logical array into a 1-0 array, i.e. as an array-valued "NOT" operator which has a byproduct of converting to a numeric.
20

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
Improve this answer

2 Comments

Microsoft successfully implemented a BITAND/BITOR that accepts only 2 parameters (instead of arbitrary). I'm expecting a new function, like BITORS/BITANDS somewhere around 2025 to fix this.
2025? Such optimism!
8

A clean and relatively easy way around this is to use nested IF statements:

IF(A1:A3 > 0, TRUE, IF(B1:B3 > 0, TRUE, FALSE))

This returns TRUE if the number in A is greater than 0, then, if not, returns TRUE if the number in B is greater than 0, but otherwise returns FALSE.

Basically you are just writing your own OR function. You could do the same with AND. As long as you have conditionals and nots, you can make everything else out of them.

Improve this answer

Comments

4

I agree with @barry houdini but would also like to point, it doesn't matter whether the columns are the same or different. + or * will solve it for OR/AND

To OR:

=(A1:A3="A")+(B1:B3="B")

To AND:

=(A1:A3="A")*(A1:A3="B")

here is a working sample formula

={MAX(IF((('Stock Statement Dec2019'!$A$2:$A$676=Synthesis!$A$12)*('Stock Statement Dec2019'!$B$2:$B$676=Synthesis!A13));'Stock Statement Dec2019'!$Q$2:$Q$676))}
Improve this answer

Comments

3

I did an Array formula for the median of certain data within a table. For example, within the data there were groups X,Y, and Z. I wanted to get the median for two types of data, X and Y, so could not use the OR statement within the array.

What I did was create a new column in the original data set. It was IF column A=X or A=Y, display "1", IF Not display "0"

I then wrote the Array formula based on the new column, since all of the data groups were reclassified in this column as either 1 or 0, I no longer needed the OR statement within the array.

That worked for me, and I can easily copy and paste new data into the original table without messing up this new IF statement so it is easy to update.

Improve this answer

Comments

3

You can use the IF function with your array results and it will give you series of 1 and 0 as OR. For example:

IF(((A1:A3>0)+(B1:B3>0)),1,0)

I find that this is the easiest way.

Improve this answer

1 Comment

((A1:A3>0)+(B1:B3>0) itself outputs 0, 1 or 2. Given that 2 is the same truthy as 1, so it's better to use ((A1:A3>0)+(B1:B3>0)) directly :)
1

Heres an easier way.

Suppose you have an array X that contains:

Project Apples Fox Fireflies 0

You want to return an array that has elements that are True if the element is 0 or Project or False otherwise. As discussed OR does not work. This is because OR accepts multiple inputs, including a range of cells. It interprets the array as multiple inputs to be ORed together. Concat() will do the same thing.

Many sources tell you to use the addition function. This will lead to problems when your test can return true results for both tests.

For example: Let(X,{"Project","Apples","Fox","Fireflies",0},(Right(X,1)="s")+(Left(X,1)="F"))

returns:

{0,1,0,1,0}+{0,0,1,1,0} = {0,1,1,2,0}

This often works because Excel interprets 0 as False and any other number as True. So 2 is as true as 1. But it can run into problems if you try to do a logical test on it. For example, if you try to invert the result by using 1<>{0,1,1,2,0} you get back 1<>{1,0,0,1,1} instead of {1,0,0,0,1}. If you know this can happen, you can be careful, but a better way to do it is invert the tests, then multiply, which is the equivalent of AND , then invert again. It sounds like more work but its not, and it ALWAYS works, and it even returns a Boolean result. Wrap everything in parentheses because Excel seems to have trouble with the order of operations with arrays and you have to make it explicit.

The following should work right under all circumstances.

Let(X,{"Project","Apples","Fox","Fireflies",0},1<>(Right(X,1)<>"s")*(Left(X,1)<>"F"))

1<>{1,0,1,0,1}*{1,1,0,0,1} = 1<>{1,0,0,0,1} = {True,False,False,False,True}

Improve this answer

1 Comment

Hello Michael and welcome to Stack Overflow. Your Answer could use some more clear formatting, especially around your code examples. You can use backticks to create blocks of inline code such as 1<>{0,1,1,2,0}, which makes answers containing code easier to read. You can find more information on how to write good answers in the help center.
0

You can use Sign() with the (Condition)+(Condition) to Cleanly cap at 1.

So for your example:

SIGN( (A1:A3 > 0) + (B1:B3 > 0) )

SIGN Array OR Example

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.