Save an hour of work a day with these 5 advanced Excel tricks
Work smarter, not harder. Sign up for our 5-day mini-course to receive must-learn lessons on getting Excel to do your work for you.
How to create beautiful table formatting instantly...
Why to rethink the way you do VLOOKUPs...
Plus, we'll reveal why you shouldn't use PivotTables and what to use instead...
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.
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?
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:
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: