Calling all Excel Logical Operators

Blog Downloads:

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.

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.

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.

Figure1

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.

Figure2

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.

Figure3

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

2 Comments

  1. Logical true | Importtiochula July 22, 2011 at 9:41 pm - Reply

    […] Calling all Excel Logical Operators | VFM […]

  2. Nancy October 2, 2011 at 5:55 am - Reply

    Thanks for the share!
    Nancy.R

Leave A Comment