CODEWITHSUNDEEP
arraysexcel
PPC
PPC

Reputation: 1913

How to apply a logical OR to an array in Excel

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

Upvotes: 38

Views: 79045

Answers (8)

Seth Kirk
Seth Kirk

Reputation: 1

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

Upvotes: 0

Michael Michalski
Michael Michalski

Reputation: 11

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}

Upvotes: 1

ExcelEverything
ExcelEverything

Reputation: 311

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

Upvotes: 16

Sagar
Sagar

Reputation: 191

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

Upvotes: 4

Sapan Gupta
Sapan Gupta

Reputation: 108

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.

Upvotes: 3

Byron
Byron

Reputation: 31

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.

Upvotes: 3

user2623027
user2623027

Reputation: 109

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.

Upvotes: 8

barry houdini
barry houdini

Reputation: 46341

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

Upvotes: 38

Related Questions