Excel OR function doesn't allow spill range?

Question

how do i get OR function to return a spill range?

For example, in excel 365, cell A1 and B1, I have a spill range of sequence(20). Which creates 2 sequence of 1-20. In c1, I tried to put in "=OR(A1#>1,B1#>1)". I would expect a result of 20 rows with first being false and other being true. But the result only return TRUE in c2. any idea how i can populate the entire column?

TIA!

i was able to solve this with "=IF(A1#>1,TRUE,IF(B1#>1,TRUE))". but can I accomplish this by directly using OR? — J Vegetable, Jul 22 '21 at 22:47
No. `OR` is used to determine if **any** conditions in a test are TRUE. As such, it only returns a single value. — Ron Rosenfeld, Jul 22 '21 at 23:37

score 0 · Answer 1 · answered Jul 18 '22 at 08:12

In Excel, boolean results can be used as numbers where True=1 and False=0. So, for OR operations you can use + and for AND operations you can use *.

In your case you could write =(A1#>1)+(B1#>1)>0 if in need of OR. For AND you can rewrite as =(A1#>1)*(B1#>1)>0

score 0 · Answer 2 · answered Jan 18 '23 at 07:39

There is another way to achieve this if you have Office365 using BYROW.
As mentioned in comments, OR returns a single value for an entire range, and a spilled array formula counts as a single range. The BYROW function splits a range into rows and applies another function to each row individually.
Applied to your question:

=BYROW(A1#:B1#, LAMBDA(row, OR(row>1)))

I understand that this isn't really much more efficient in this instance than the solution you already have working, but hopefully the knowledge could help you or someone in a situation with more complex arguments to pass (e.g. many OR conditions to check).

Excel OR function doesn't allow spill range?

2 Answers2