Excel Test For One of Two Conditions

A shorter alternative

In Excel it is quite common to test a cell for either a zero or a blank. If either of these two entries are found then you do a particular calculation. There is an easy way to handle this.

Typically you would use an OR function to determine if a cell contains either a blank or a zero. As an example see the image below.

If there is no Actual figure in row 2 we don’t want to calculate the variance in row 4. The formula in cell B4 will normally look like this.

=IF(OR(B2="",B2="0"),0,B2-B3)

In the case where you are checking the same cell to see if it contains one of two or more things you can use what is called “array syntax” to shorten the formula. In the above case the formula could be rewritten as

=IF(B2={"","0"},0,B2-B3)

This basically means that the cell is compared to each of the entries between the curly brackets (braces).

In this case two comparisons are made. Cell B2 is compared to a blank and then compared to zero. If either or both are TRUE then TRUE is returned.

This works just like an OR function.

You can see this shortens the formula. The only downside is that most Excel users are unaware how the braces work so they may be confused. You may want to add a cell comment to explain the formula.

You can test for as many entries as you like, just separate them with commas.

WARNING: You can’t use cell references between the curly brackets.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.