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:
Operator | Description | Example |
" = " | 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 " |
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]:
A | B | C | |
1 | Salesperson | Jan_Sales | Feb_Sales |
2 | Aaron | 12,000 | 10,000 |
3 | Betty | 10,000 | 18,000 |
4 | Carl | 8,000 | 25,000 |
5 | Daisy | 11,000 | 11,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.
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.
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.
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]:
A | B | C | |
1 | Salesperson | Jan_Sales | Feb_Sales |
2 | Aaron | 12,000 | 10,000 |
3 | Betty | 10,000 | 18,000 |
4 | Carl | 8,000 | 25,000 |
5 | Daisy | 11,000 | 11,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:
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]:
A | B | C | |
1 | Salesperson | Jan_Sales | Feb_Sales |
2 | Aaron | 12,000 | 10,000 |
3 | Betty | 10,000 | 18,000 |
4 | Carl | 8,000 | 25,000 |
5 | Daisy | 11,000 | 11,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:
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]:
A | B | C | |
1 | Salesperson | Jan_Sales | Feb_Sales |
2 | Aaron | 12,000 | 10,000 |
3 | Betty | 10,000 | 18,000 |
4 | Carl | 8,000 | 25,000 |
5 | Daisy | 11,000 | 11,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:
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.
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.
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:
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.
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