1. Using logical functions in formulas
We will take our use of formulas to the next level by finding ways to add conditions through logical functions.
2. Logical functions
Firstly, what is a logical function? Logical functions allow us to test whether a condition is true or false. Using logical functions, we can create conditional formulas in Excel, and based on the conditions we set, we can get different outputs.
This video will focus on four logical functions - AND, OR, NOT, and IF.
3. The AND() function
The AND function tests whether one or more conditions are true. If the logical tests are all true, the result is TRUE; otherwise, FALSE is returned.
Logical functions are great for creating flags based on specific conditions of two or more variables in the dataset. For example, we have data on products and total revenue per month. We can use the AND function to create a TRUE or FALSE flag for Pears with more than $2,500 total revenue.
4. The OR() function
The OR function is similar to AND, but this time if one of the conditions tested is true, it will result in a TRUE outcome.
Using the same data as before, we can use the OR function to create a flag to identify records for either Pears or where the total revenue was more than $2,500.
5. The NOT() function
The NOT function is different as it only takes one logical argument. In addition, the function's output will also be the opposite of the condition set. Better to examine this with an example.
We want to flag total revenue of $2,500 or less. We can set the logical argument to test whether total revenue exceeds 2,500. Excel will look if the outcome of the condition is TRUE or FALSE, and then give the opposite outcome. Our column shows TRUE for any total revenue of $2,500 or less.
6. The IF() function
Finally, the IF() function allows us to test whether a condition is true or false, and depending on the result, we can apply different outcomes, such as a text string.
How about a real-life scenario? We want to go outside to visit some shops. First, we should check if it is raining. If it is raining, we should take our umbrella with us. Otherwise, we can leave it at home.
7. Combining logical functions
The default syntax of the IF function has one condition to test. However, we will find ourselves in situations where we must test multiple conditions to find a true and false outcome. To do this in Excel, we can combine logical functions like AND, OR, and NOT with our IF function to create nested formulas that can test multiple conditions.
These nested formulas work well when we have multiple conditions to test to obtain two possible outcomes. What if we have more than two possible outcomes?
8. Combining IF() functions
We can nest IF functions into each other to test multiple conditions to produce multiple outcomes. For example, we test our first condition; if true, we have one particular outcome. If false, we test our second condition using another IF function. If this second condition is true, we have our second outcome. If it is false, we have our third outcome.
How about another weather scenario? Our first logical test is to check if it is snowing outside. If yes, we want to wear our big winter jacket. However, if it is not snowing, we want to check if it is raining. If this is true, then we should take our umbrella instead. However, if it is not raining, we can wear normal outdoor clothes.
9. Let's practice!
Time to practice some logical functions!