Forget telephone operators, who almost always put you on hold, today we’re talking about Excel logical operators (also called Excel logical functions).
A logical operator can simply be defined as a function that returns certain values if a logical test is true or false.
We will look at three of the key Excel logical operators; IF, AND and OR.
The format of the IF statement is as follows:
Example: Below is a table with people’s dates of birth. We want to find out which of the people are over 21. Let’s assume that today’s date is 31 December 2011.
Normal 0 false false false EN-GB X-NONE X-NONE
See the formulas for the calculation in Figure 1 below.
Figure 1: Calculation for IF Function Example
If you found that Bob (the first Bob that is) and John are the only people over 21 then you’d be correct.
AND
The format of the AND statement is as follows:
Example: We want to find whether the date 31 May 2011 is in between 30 June 2011 and 31 December 2011.
We can see the calculation for the above in Figure 2 below.
Figure 2: Calculation for AND Function Example
If you got FALSE you’d be correct.
This may seem like an easy example but its implications are far reaching. Imagine if you had lots of dates and you wanted to find which dates are between two particular dates, a start date and an end date. I bet you could copy this formula across or down a row to find which dates meet the AND criteria.
Remember our Blog tutorial on Car Indicators – How they can help you model in Excel? We did a similar thing in that Blog tutorial.
OR
The format of the OR statement is as follows:
Example: The below is a table with test results for two tests recently taken by students. We want to find the students that either scored over 75% in one test or had an average of more than 65%.
See Figure 3 for the OR calculation.
Figure 3: Calculation for OR Function Example
You'll notice that both the Bob's met the criteria.
If you like this article, there are plenty more tips, tricks and worked examples in our Excel Functions training course.
Check out our Excel and Financial Modelling training courses.
A logical operator can simply be defined as a function that returns certain values if a logical test is true or false.
We will look at three of the key Excel logical operators; IF, AND and OR.
Note: You can follow the examples by downloading the accompanying Excel spreadshset and YouTube video.IF
The format of the IF statement is as follows:
= IF (Logical Test, Value if logical test is true, Value if logical test is false)The IF statement checks if a logical test is true or false and returns a certain value based on the outcome. Let’s look at an example.
Example: Below is a table with people’s dates of birth. We want to find out which of the people are over 21. Let’s assume that today’s date is 31 December 2011.
Normal 0 false false false EN-GB X-NONE X-NONE
Person | Date of Birth |
Bob | 31 Aug 1990 |
John | 30 Sep 1995 |
Bob | 31 Dec 1992 |
Jane | 30 Jun 1984 |
Sally | 31 Dec 1991 |
If you found that Bob (the first Bob that is) and John are the only people over 21 then you’d be correct.
AND
The format of the AND statement is as follows:
= AND(Logical Test 1, Logical Test 2...)The AND statement returns TRUE if all the logical tests are true and FALSE if any one of the logical tests is false. Let’s look at an example.
Example: We want to find whether the date 31 May 2011 is in between 30 June 2011 and 31 December 2011.
We can see the calculation for the above in Figure 2 below.
If you got FALSE you’d be correct.
This may seem like an easy example but its implications are far reaching. Imagine if you had lots of dates and you wanted to find which dates are between two particular dates, a start date and an end date. I bet you could copy this formula across or down a row to find which dates meet the AND criteria.
Remember our Blog tutorial on Car Indicators – How they can help you model in Excel? We did a similar thing in that Blog tutorial.
OR
The format of the OR statement is as follows:
= OR(Logical Test 1, Logical Test 2...)The OR statement returns TRUE if any of the logical tests are true and FALSE if all of the logical tests are false. Let’s look at an example.
Example: The below is a table with test results for two tests recently taken by students. We want to find the students that either scored over 75% in one test or had an average of more than 65%.
Person | Test 1 (%) | Test 2 (%) |
Bob | 76.00% | 65.00% |
John | 53.00% | 74.00% |
Bob | 67.00% | 65.00% |
Jane | 45.00% | 65.00% |
Sally | 65.00% | 60.00% |
You'll notice that both the Bob's met the criteria.
If you like this article, there are plenty more tips, tricks and worked examples in our Excel Functions training course.
Check out our Excel and Financial Modelling training courses.
No comments:
Post a Comment