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.
The IF function (00:24)
The IF function checks if a condition is true. It returns one value if the condition is true, and another value if the condition is false. An example of using this would be to measure if some number, like total revenue, is above or below a target.
IF takes three arguments. First is the logical test, which is the condition we want to evaluate. Second is the value to return if the condition is true. Third is the value to return if the condition is false.
The AND function (02:24)
AND accepts multiple logical conditions and returns true only if all the conditions are true. If any one of the individual conditions is false, AND returns false. If we have two targets, such as a revenue target and an orders target, we can use AND to check if both targets are achieved.
AND can be combined with IF in situations where you want to check multiple conditions instead of one.
The OR function (04:44)
The OR function accepts multiple logical conditions and returns true if any one of them is true. It will return false if all of the individual conditions are false. OR can be combined with the IF statement to check if any of our company targets have been met.
Logical Operators (05:59)
When creating conditions for logical tests, these are the logical operators you can use:
- = Equals
- <> Not equal to
- > Greater than
- >= Greater than or equal to
- < Less than
- <= Less than or equal to
In the previous lesson, we learned about the average max and min functions. In this lesson, we'll learn how to use Excel's logical functions. Logical operators such as and, if, and or check if a certain condition holds, and then allow you to perform an action based on whether the condition is true or false. Let's start to offer a look at logical operators with the if statement. This operator is very similar to the countif and sumif commands we've used previously. If simply checks if 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 our three-month period, we'll say 500,000, 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 for 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 800,000.
And now the first if statement changes to false, and the second, to not applicable.
I'll 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 now we'll 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 right N/A 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 800,000.
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 dovetails 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'll write equals or, open the bracket, and enter 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 500,000, 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 we'll increase the target orders to 26 and the target revenue to 800,000.
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.