Ranges in AND or OR (nice and simple Q)

[Willb]

Hi guys,

Is there any way to use ranges in AND and OR?

For example:

Currently using: OR(A1="",B1="",C1="",D1="")

Would like to use something to the effect of: OR(A1:D1="")

Is this possible? (keeping in mind I want to use less code)

Cheers

 

[goliath624]

You might want to use COUNTIF

COUNTIF(A1:D1,"") will return the number of blanks in the range. Then you can just nest that into an IF function to determine if it is greater than zero.

 

[lenze]

Actually, just (IF(COUNT(A1:D1),TRUE,FALSE)

lenze

 

[pgc01]

Currently using: OR(A1="",B1="",C1="",D1="")

Would like to use something to the effect of: OR(A1:D1="")

Hi

You can use your formula:

=OR(A1:D1="")

but since it's an array formula you have to confirm it with CTRL-SHIFT-ENTER instead of just ENTER.

 

[Willb]

Thanks for the replies guys,

PCG01, do the use of array formulas increase Excel's processing speed?

This part of the formula is repeated thousands of times in a call centre roster that we use, so I'm trying to decrease characters and obviously loading times etc

 

[pgc01]

Thanks for the replies guys,

PCG01, do the use of array formulas increase Excel's processing speed?

This part of the formula is repeated thousands of times in a call centre roster that we use, so I'm trying to decrease characters and obviously loading times etc

No, they usually decrease Excel's processing speed.

This is an equivalent non-array formula:

=COUNTBLANK(A1:D1)<>0

I'm just leaving, but you may test the options and check which one works faster.