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!
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
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).
ORis used to determine if any conditions in a test are TRUE. As such, it only returns a single value.