4. Logical Operators

 
Subtitles Enabled

Sign up for a free trial to continue watching this lesson.

Free trial

Overview

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.

Lesson Notes

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

= Equals
<> Not equal to
> Greater than
>= Greater than or equal to
< Less than
<= Less than or equal to

Transcript

Logical functions such as AND, IF and OR accept tests that allow you to perform specific actions depending on whether those logical tests are True or False.

Let's start off with a look at logical functions with the IF function.

The IF function simply checks if a logical test is True, and returns one value if it is True and another value if the logical test is False.

Let's see it in action by creating a Target revenue for a three month period of $700,000.

In the selected cell I'll write “=if” and open a bracket.

And I must first enter my logical test, which will be the Total Revenue is greater than, or equal to the Target Revenue.

And if this test is True, I'll just return True, and if it's False, I'll return False.

I'll then close the bracket and press Enter.

And as the Total Revenue is greater than the Target Revenue, the cell returns True.

If I move the Target Revenue up to $800,000, obviously the IF function will now return False.

I'll return to the previous target with Ctrl + Z.

We can actually return any value from an IF function and not just True or False.

Let's say I want to return the difference between Total Revenue and the Target if the Revenue exceeds the Target.

Again, I'll write an IF function and the logical test will be the same if Total Revenue is greater than or equal to the Target.

If this is True, I'll return the difference, which is Total Revenue minus the Target, otherwise I'll return “N/A”.

I'll then close the bracket and press Enter.

And because Total Revenue exceeds the Target Revenue, I get a figure of $92,000 which is the difference between the Total and the Target.

If I bump the target up to $800,000, the IF function will now return “N/A”.

Let's now move on to the AND function.

The AND function accepts multiple logical tests and returns True only if all logical tests are True.

I'm going to create a new order target total of 20.

I will now check if both targets are achieved.

So I'll write “=and” and open a bracket, and the first logical test will be the Total Revenue is greater than or equal to the Target.

And the second logical test will be if the number of orders is greater than or equal to the Target number of orders.

I'll then close the bracket and press Enter.

And obviously my answer is going to be False because the Target Revenue is not met.

If I change my Target Revenue back to $700,000, the AND function is now satisfied.

The AND function can accept multiple logical tests, not just two as we've seen here.

It's often used in conjunction with the IF function when you need to check multiple logical tests.

In the next example I'm going to check if both targets are met.

And if both Targets are met, I'm going to return the difference between the Actual number of orders and the Target number of orders.

Otherwise I'll return “N/A”, so I’ll write “=if” and open the bracket.

Excel now asks me for a logical test and here I'll place an AND function.

The logical test will be as before, Total Revenue is greater than or equal to Target Revenue.

And the second logical test is the number of orders is greater than or equal to the Target number.

I'll close the bracket and write a comma.

If this value is true, I'm going to take the difference between the number of orders and the Target, and if it's not True I'll simply return “N/A”.

And as expected, because both Targets are met, the cell returns the value of 3, which is the difference between the Total number of orders and the Target number of orders.

The last function I want to show you in this lesson is the OR function.

The OR function accepts multiple logical tests and returns True if at least one logical test is True.

Let's see it in action by finding out if at least one Target is achieved.

So I'll write “=Or” and my first logical test will be Total Revenue is greater than or equal to Target Revenue.

And the second logical test will be the number of orders is greater than or equal to the Target.

And because both Targets are met OR returns True.

If I now change the Target revenue to $800,000, OR will still return True because one logical test still holds.

However if I move my Target orders to 25, neither Target is met and OR now returns False.

In this lesson we've used the “Greater than” or “Equal to” operator in all of our logical tests.

However, you can use any one of these six logical operators on the right in your formulas.

Make sure to take note of the “Not equal to” option, which you may not have seen before and which can be quite useful.