OR/AND functions returning ranges

[gpellacani]

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

[#2]

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

[#3]

@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

[#4]

@gpellacani Glad you figured it out, although I don't understand what you are trying to achieve.

[#5]

@Riny_van_Eekelen what I'm trying to achieve is to return A (a single value) if ALL cells in the B1:B15 range are either void or 0, B otherwise (i.e. if ANY cell in the B1:15 range is neither void nor 0).

The function you originally suggested returns A for EACH cell in the B1:15 range which is either void or 0, B otherwise, hence a 15-cell range

Have a great Sunday!

Gianfranco