Sign in or start a free trial to avail of this feature.
4. Logical Operators
Operators such as AND, IF and OR give you much more control when performing data queries in Excel. Find out how to use them in this lesson.
AND, IF and OR operators
=IF: Performs a logical test, returning a value if test is TRUE and another if test is FALSE
=AND: Returns TRUE if ALL logical tests return TRUE
=OR: Returns TRUE if AT LEAST ONE logical test returns TRUE
- Use AND and OR to create multiple logical tests for an IF statement
Operators that can be used in logical tests
<> Not equal to
> Greater than
>= Greater than or equal to
< Less than
<= Less than or equal to
(soft upbeat music) - [Instructor] Logical operators such as and, if, and or check if certain condition holds and then allow you to perform an action based on whether the condition is true or false. Let's start off our look at logical operators with the if statement. This operator is very similar to the count if and some if commands we've used previously. If simply checks if a condition is true and then returns one value if it is true and another value if the condition is false. Let's see it in action by creating a target revenue for a three month period, we'll say 500000, and then we'll check, using the if statement, if the target is achieved. So, I'll write equals if and then open the bracket, and Excel now tells me to enter the logical test, which is total revenue is greater than or equal to the target revenue. If this condition holds, I'll return true, and if it doesn't, I'll return false. I'll then close the bracket and press enter. And obviously, because total revenue is much greater than target revenue, the revenue target is achieved and the if statement returns true. We can actually return any value from an if statement and not just true or false. For example, maybe I'd like to return the difference between the actual revenue and the target if the revenue exceeds the target. I'll create a new if statement, I'll put in the same logical test, and now, if the value is true, I'll return total revenue minus target revenue, and if the value is false, I'll just return not applicable.
Press enter, we can now see the difference between the two numbers is returned, because the logical test holds. To see what happens when an if statement doesn't hold, let's just change the target revenue to 800000.
And now, the first if statement changes to false and the second to not applicable. And now switch back with Ctrl Z.
Now, let's move on to the and statement.
And accepts multiple conditions and returns true if all of the conditions hold true. Let's see it in action by creating a new target order of 20 and I will check both of these targets with an and statement, I'll write equals and, and then open the bracket and enter the first logical test, which is total revenue is greater than or equal to target revenue. And the next logical test is that the number of orders is greater than or equal to the number of target orders. We'll close the bracket, press enter, and obviously, this returns true, because both of the conditions hold. In this instance, we've only used two logical tests, but the and function can actually accept as many as you want. And is often used in conjunction with the if statement when I want to check multiple conditions instead of just one. In this cell, if both targets are achieved, let's display the difference between the actual order number and the target order number. If it's not achieved, we'll simply write NA like before. So, I'll start off with equals if, and for my logical test, I'll create an and function.
I'll open the bracket and add my first logical test, which is total revenue is greater than equal to target revenue, I'll write a comma, and then create the second logical test.
I'll then close the bracket, and if the and statement holds true, I'll show the difference between the order numbers.
And if it doesn't, I'll write not applicable.
Close the bracket and press enter.
Given that both targets are achieved, the statement holds true and three is displayed. Now, watch what happens when I move the target revenue to 800000.
The and statement now returns false, and as a result, we return not applicable if the order target is exceeded. So as you can see, the and statement dove tails very well with the if statement when you want to check multiple criteria. Now, let's move on to or. The or function returns true if at least one of the criteria entered is true, unlike and, which checks if all the criteria are true. Let's see what or returns when we put our target criteria into the formula. We write equals or, open the bracket, and enter our two criteria.
And we can see that even though only one of our criteria holds, or returns true. If I move the target revenue back to 500000, both our criteria will hold true and or still returns true. For or to return false, all of the statements within the or statement must also return false, so I'll increase the target orders to 26 and the target revenue to 800000, and now, or returns false, because no target is achieved. Or can also be used in conjunction with the if statement if you wanted to check if at least one criteria holds true in your logical test. When creating conditions for and, or, and if functions, I've only used the greater than or equal to sign up to now. However, you can use any of these six logical operators when creating conditions. These operators give you great flexibility when using logical functions. So that you get practice using some of these logical operators, I've included a couple of exercises below, which you can use to develop your skills. You can test your answers at any stage by changing the target to different values above and below the actual revenue and order number. I'll also include the answers in the after file that can be downloaded underneath this video.