Forum Discussion
OR/AND functions returning ranges
Hi,
does anyone know if there is any way to have OR Function in excel return a range rather than a singe value when it is fed two or more ranges?
E.g. I want XL to return "A" in cell C1 if all cells in range B1:B3000 are either void or 0, "B" otherwise.
As you may figure out, I don't want to manually AND 3000 OR functions, once for each cell in the B1:3000 range.
Also, I would prefer not adding an intermediate column to store the 3000 OR functions, before AND-ing them.
I entered the following formula in C1
=IF(AND(OR(B1:B3000="",B1:B3000=0)),"A","B")
I would expect "OR(B1:B3000="",B1:B3000=0)" returns a 3000 rows-1column range, each cell value being either
- TRUE if the corresponding cell in range B1:B3000 is either void or 0
- FALSE otherwise
Instead, "OR(B1:B3000="",B1:B3000=0)" returns a single value, being TRUE if any cell in range B1:B3000 is either void or 0, FALSE otherwise.
Thanks to anyone helping
4 Replies
gpellacani On a slightly smaller scale, is this what you have in mind? (See picture)
Formula used in C1:
=IF((B1:B15="")+(B1:B15=0),"A","B")Riny_van_Eekelen thank you so much for helping.
The formula you suggested to enter in C1 didn't exactly do what I needed, as it returns a range rather than a single value, while I need the OR function to return a range, and the AND an IF functions to return a single value (guess I was not clear enough) out if it.
It does the job though, once an AND formula is added:
=IF(AND((B1:B15="")+(B1:B15=0)),"A","B").
Guess I need to look back to how arithmetic operators work on logical values in XL
Again, thanks a lot, you kinda saved my day.
Gianfranco
gpellacani Glad you figured it out, although I don't understand what you are trying to achieve.
