Excel's AND & OR functions

Once you've got a handle on the TRUE and FALSE functions combined with Excel's logical operators, it's time to learn about a couple of new functions that will allow you to combine your knowledge in totally new ways: the AND and OR functions. AND and OR do just what you would expect: check to see whether multiple conditions are true in various ways.

The AND function

The AND function checks to see whether multiple conditions are true, returning TRUE only if all logical statements within it are satisfied. The formula for the AND function is as follows:

=AND(logical_expression_1, logical_expression_2...)

You can add as many logical_expressions to the end of the function as you'd like, allowing for complex chains of logic within the function. Let's take a look at some sample applications of AND to get a better handle on how it works:

=AND(FALSE, FALSE, FALSE)
Output: FALSE

=AND(FALSE, FALSE, TRUE)
Output: FALSE

=AND(TRUE, TRUE, TRUE)
Output: TRUE

The first and second examples above return FALSE, because not every logical_expression within them are TRUE. The third, on the other hand, returns TRUE, because every logical expression contained within the formula is TRUE.

Let's check out AND with some actual logical statements rather than the vanilla TRUE and FALSE values:

=AND(6>3, 8<12)
Output: TRUE

The above returns TRUE, because both of the logical statements it contains are TRUE: six is greater than three, and eight is less than twelve.

Here are some more examples of the AND function in action, using more complex logical statements. Can you figure out why each of the following evaluates as such?

=AND(6<>7, 123<4)
Output: FALSE

=AND("SnackWorld"="SnackWorld", 12>=12)
Output: TRUE

=AND(6=6, 7=7, 8=8, 9=17)
Output: FALSE

The OR function

The OR function works much the same as the AND function, with one crucial difference: OR will return TRUE if one or more of the logical statements inside of it are TRUE. The formula for OR is as follows:

=OR(logical_expression_1, logical_expression_2...)

Like AND, OR can take as many logical_expressions as you want to feed it. Let's take a look at some examples to cement our understanding of how it works:

=OR(FALSE, FALSE, FALSE)
Output: FALSE

=OR(FALSE, FALSE, TRUE)
Output: TRUE

=OR(TRUE, TRUE, TRUE)
Output: TRUE

The first example above returns FALSE because all of the statements within it are FALSE. The second and third, on the other hand, return TRUE because at least one of the statements within them are TRUE. Note the difference between this and the output for AND above.

Combining AND and OR

By now, you're probably wondering whether it's possible to combine AND and OR functions into a single formula. The answer is a resounding yes. You can chain AND and OR into statements as complex as you desire. When evaluating these statements, it's helpful to go from the inside out to figure out whether they are TRUE or FALSE. Start in the innermost parentheses, which are evaluated first, and work your way outward. Here's an example:

=AND(OR(6>3, 7<2), OR(56<>12, 78<=3))
Step 1: =AND(OR(TRUE, FALSE), OR(TRUE, FALSE)
Step 2: =AND(TRUE, TRUE)
Output: TRUE

Let's take a look at another example of complex logic chaining:

=OR(AND(7<9, "Cookies"="Gummies"), AND(1<>2, 9<=9), AND(8=9, 10>11))
Step 1: =OR(AND(TRUE, FALSE), AND(TRUE, TRUE), AND(FALSE, FALSE))
Step 2: =OR(FALSE, TRUE, FALSE)
Output: TRUE

There you have it — now you can use the AND and OR functions to evaluate complex criteria easily!

Comments