Using the AND + OR functions

I struggle to use the AND and OR functions. Could you explain how they should be used?

By Neale Blackwood

AND and OR functions are typically used with the IF function. They allow you to examine multiple logical tests in a single function rather than use numerous IF functions to achieve the same result. Examples of logical tests are A1=B1, A1>B1 and A1>=0. In the same way that a logical test gives a true or false result, both functions also return true or false.

The syntax (structure) of the two functions is identical. Both have logical tests separated by commas. The difference is how each function views those logical tests. Let’s look at examples using the same three logical tests.

=AND(A1>=0,A2>=0,A3>=0)

=OR(A1>=0,A2>=0,A3>=0)

The AND function will only return a TRUE result if A1 and A2 and A3 are greater than or equal to zero. If any of those cells are less than zero the AND function will return a FALSE result.

The OR function will return a TRUE result if A1 or A2 or A3 is greater than or equal to zero. The only time an OR function returns a FALSE result is when all the logical tests are FALSE. In this case if all three cells are less than zero.

You can use an AND or an OR function in a cell to return a TRUE or FALSE result. This is useful for some validation tests.

Examples of using the functions in an IF function follow:

=IF(AND(A1>=0,A2>=0,A3>=0),”All > = zero”,”At least one cell less than zero”)

=IF(OR(A1>=0,A2>=0,A3>=0),”At least one >= zero”,”All cells less than zero”)

These will display messages depending on the contents of A1,A2 and A3. Excel 2003 can have up to 30 logical tests, Excel 2007 can handle up to 255 logical tests.

An example to avoid a #DIV/0 error message is:

=IF(OR(A1=0,A2=0),0,(B1/A1)+(B2/A2))

To check three cells all have Y in them you could use:

=IF(AND(A2=”Y”, B2=”Y”, C2=”Y”),”Send email”,””)

The “” at the end of the IF function will display a blank cell if the AND function returns false. When referring to text you need inverted commas around the text. In logical tests upper and lower case is treated the same.

Warning: It is very important to test the logic of your formula. IF,AND and OR functions are common causes of spreadsheet errors and need to be tested.