Excel Functions: Logical Testing

Many data analytics tasks are based on conditions or criteria. If a datum satisfies a condition, such as reaching a numerical threshold, falling between certain date and time, or containing certain text, further operations are performed. Logical testing is the process of testing whether certain criteria are met to determine the desired actions. This blog introduces some common functions used to perform logical testing.

Boolean values are a type of data (like numerical, datetime, and text) that can only take two values, TRUE and FALSE.

For example, the formula = 2 > 1 returns the Boolean value TRUE and the formula = 3 > 4 returns the Boolean value FALSE.

Below is a list of common comparison operators used in Excel:

  if   function

The   if   function tests a criterion, performs a specified operation when the criterion is met, and performs a specified operation when the criterion is not met. It has the following syntax:

= if(logical_test,[value_if_true],[value_if_false])

Assume the following dataset [you may paste it into Excel]:

The company pays a 2% bonus on sales to its salespersons if the salesperson’s sales in January exceeds $10,000.

Example 1:
The formula = if(B2 >= 10000, “Yes”, “No”) tests if Aaron is eligible for the bonus in January. If his sale in January is at least $10,000 (the test returns TRUE), the text “Yes” is displayed; Otherwise, the text “No” is displayed.

Example 2:
The formula = if(B2 >= 10000, B2*2%, 0) calculates the bonus to be paid to Aaron. If his sale in January is at least $10,000 (the test returns TRUE), bonus to be paid is 2% of the sales; Otherwise, the bonus is zero.

  and   function

The   and   function tests multiple criteria and returns TRUE only when all the logical tests are TRUE. It has the following syntax:

= and(logical1,[logical2],…)

Example 1:
The formula = and( 0 > 1, 2 > 1) returns FALSE because the first logical test is FALSE.

Example 2:
The formula = and (1 > 0, 2 > 1, 3 > 2) returns TRUE because all of the logical tests are TRUE.

  or  function

The   or   function tests multiple criteria and returns TRUE when at least one of the logical tests is TRUE. It has the following syntax:

= or(logical1,[logical2],…)

Example 1:
The formula = or(0 > 1, 2 > 1) returns TRUE because the second logical test is TRUE.

Example 2:
The formula = or(0 > 1, 1 > 2, 2 > 3) returns FALSE because all the logical tests are FALSE.

 not  function

The   not   function negates the result of a logical test. It has the following syntax:

= or(logical)

Example:
The formula = not(0 > 1) returns TRUE because it negates the FALSE result for the logical test 0 > 1.

Assume the following dataset [you may paste it to Excel]:

The company pays a 2% bonus on sales to its salespersons if the salesperson’s sales is at least $10,000 in either January and February.

Example 1:
The formula = if(and(B2 >= 10000, C2 >= 10000), sum(B2,C2) * 2%, 0) calculates the bonuses to be paid. Below are the steps performed by the formula:

1. Run the   and   function to test if sales in both months are at least $10,000
2. If the   and   function returns TRUE, the bonus is the total sales in the two months multiplied by 2%
3. If the   and   function returns FALSE, no bonus is to be paid

Because Carl’s sales in January total less than $10,000, the   and   function returns TRUE and a 2% bonus on his total sales will be paid.

Assume the following dataset [you may paste it to Excel]:

The company pays a 2% bonus on sales to its salespersons if the salesperson’s sales total at least $10,000 in either January or February.

Example 1:
The formula = if(or(B2 >= 10000, C2 >= 10000), sum(B2,C2) * 2%, 0) calculates the bonus to be paid. Below are the steps performed by the formula:

1. Run the   or   function to test if sales in either January or February is at least $10,000
2. If the   or   function returns TRUE, the bonus is the total sales in the two months multiplied by 2%
3. If the   or   function returns FALSE, no bonus is to be paid

Because Carl’s sales in February exceeds $10,000, the   or   function returns TRUE and a 2% bonus on his total sales will be paid.

Assume the following dataset [you may paste it to Excel]:

The company pays a 2% bonus on sales in February only if the salesperson’s sales in February are not less than sales in January.

Example 1:
The formula = if(not(C2 < B2), C2* 2%, 0) calculates the bonus to be paid to Aaron for his sales in February. Below are the steps performed by the formula:

1. Run the   not   function to test if sales in February are NOT less than sales in January
2. If the   not   function returns TRUE, bonus is the sales in February multiplied by 2%
3. If the   not   function returns FALSE, no bonus is to be paid

Because Aaron’s sales in February are less than sales in January, the   not   function returns FALSE. As a result, no bonus is to be paid.

  switch   function

The   switch   function looks up an expression sequentially in a list of the values specified and returns a specified result until the expression is found (or not found) in the list. It has the following syntax:

=switch(expression, value1, result1, [default or value2, result2], …, value when no match)

Example:

☨NOTE: This formula returns the number 4, indicating that January 1, 2020 falls on the fourth day of the week starting from Sunday

Consider the table above with this formula = switch(B2, 1, “Weekend”, 7, “Weekend”, “Weekday”). The “expression” is B2, which returns the number 4. Because the number 4 does not match the two numbers specified (i.e., 1 or 7), the   switch   function returns the last argument (i.e., the result to be returned when the expression is not found) and indicates that 1/1/2020 is a weekday.

  ifs   function [available in Office 365 or later versions]

The   ifs   function performs a list of logical tests in sequence and returns a specified result when the first criterion is met or when no criterion is met. It has the following syntax:

=ifs([Something is True1], Value if True1, Something is True2, Value if True2, something is True3, Value if True3…)

Note: The   switch   function looks for the values in the list specified; the   ifs   function performs a series of logical tests. Knowing the difference will help you determine the best solution for each situation.

Assume the following dataset[you may paste it into Excel]:

Example:

The formula = ifs(E2>=90,”A”, E2>=80,”B”, E2>=70,”C”, E2>=60,”D”, E2>=50,”E”, TRUE, “F”) computes the letter grade of the students. Below are the steps performed by the formula:

1. If the mark of the student is at least 90, display the letter grade “A”. Otherwise, proceed to the next test.
2. If the mark of the student is at least 80, display the letter grade “B”. Otherwise, proceed to the next test.
3. This process goes on until the mark of the student is below 50 and the letter grade “F” is displayed.

Note: TRUE is used as the 6th criterion to denote that all other conditions (marks below 50) are True.

If the   switch   function is used, the formula would be incredible long with over 50 values to process. For reference, just accounting for the “A”s would look something like this:

= switch(E2, “100”, “A”, “99”, “A”, “98”, “A”, “97”, “A”, “96”, “A”, “95”, “A”, “94”, “A”, “93”, “A”, “92”, “A”, “91”, “A”, “90”, “A”).

As you can see, listing every value out can get long pretty quickly.

Learning Excel Data Analytics with Gleim

At Gleim, we know learning data analytics is vital for future certified accountants. That is why we are offering this series of data analytics blogs and continually updating all of our review materials for CPA, CMA, and CIA with the necessary information you need to pass your exam the first time!

We’ll continue our weekly blog series. Check back regularly for all exam news and study tips!

Excel Lessons
Excel Basics
Excel Shortcuts
Excel Calculation Rules
Cell References
Excel Functions
Function Basics
Working with Numbers
Working with Datetime Data
Working with Text Data
Logical Testing
Summarizing Data
Filtering and Sorting
Lookup Functions
Tables
Pivot Tables