Contact Us : 800.874.5346        International: +1 352.375.0772
Contact Us : 800.874.5346        International: +1 352.375.0772

Excel Functions: Logical Testing

Data Analytics, Excel Lesson 9: Logical Tests

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

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.

Comparison Operators

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

OperatorDescriptionExample
" = "Equal to" = A1 = A2 "
>Greater than" = A1 > A2 "
<Less than" = A1 < A2 "
>=Greater than or equal to" = A1 >= A2 "
<=Less than or equal to" =A1 <= A2 "
<>Not equal to" = A1 <> A2 "

Single-Criterion Logical Testing

  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]:

ABC
1SalespersonJan_SalesFeb_Sales
2Aaron12,00010,000
3Betty10,00018,000
4Carl8,00025,000
5Daisy11,00011,000

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.

Multi-Criterion Testing

  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.

Combining the “if” function and the “and” function

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

ABC
1SalespersonJan_SalesFeb_Sales
2Aaron12,00010,000
3Betty10,00018,000
4Carl8,00025,000
5Daisy11,00011,000

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.

Combining the “if” function and the “or” function

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

ABC
1SalespersonJan_SalesFeb_Sales
2Aaron12,00010,000
3Betty10,00018,000
4Carl8,00025,000
5Daisy11,00011,000

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.

Combining the “if” function and the “not” function

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

ABC
1SalespersonJan_SalesFeb_Sales
2Aaron12,00010,000
3Betty10,00018,000
4Carl8,00025,000
5Daisy11,00011,000

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.

Logical Test with More Than One Result

  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:

  A B C
1 Date DayOfWeek Weekday
2 1/1/2020 =weekday(A2)☨  
3      
4 1 (Sun): Weekend 7(Sat):Weekend Others: Weekday

☨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]:

  A B C D E
1 Marks Grade   Student Mark
2 90-100 A   1 51
3 80 -89 B   2 40
4 70-79 C   3 73
5 60-69 D   4 64
6 50-59 E   5 94
7 <50 F      

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!

Python Lessons
Python Basics
Conditional Statements
Loops
Functions and Modules
Numerical Python (NumPy)
Pandas Basics
Pandas Data Capture and Cleansing
Merging and Grouping
Manipulating Text and Datetime Data
Visualization
Web Scraping
Errors and Exceptions