Providing cost-effective Excel and financial modelling training courses.

## Calling all Excel Logical Operators

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.

## 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.

 Person Date of Birth Bob 31 Aug 1990 John 30 Sep 1995 Bob 31 Dec 1992 Jane 30 Jun 1984 Sally 31 Dec 1991

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 Jane 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.

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:

= 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%

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 ” target=”_self”>Excel and Financial Modelling training courses.

By | 2011-07-20T12:51:52+00:00 July 20th, 2011|Blog|2 Comments