Formulas for Testing Multiple Conditions

Overview

In the last series of articles discussing logical functions, we looked at nesting multiple “IF” statements together to test for each possible outcome in order to arrive at our final solution.

To model this approach, we looked at using common logic modeling tools like “Truth Tables” and “Hierarchical Flowcharts” to first build out the path the logic will take, then transform that logic into a complete formula we can simply paste into a SharePoint calculated column.

In this series, we’ll look at bringing in the additional logical functions of “OR” and “AND” in order to test for combinations of logical tests all within the same check.

Getting Started

 To begin, we first need to understand the logic associated with both new functions, so we’ll revisit our “Truth Table” in order to illustrate how each of these works.

 

The basics behind the “OR” function is that if any of the values are “TRUE”, the entire statement is “TRUE”, and if none of them are “TRUE”, the entire statement is “FALSE”.

We can use the “OR” function to test for up to 30 different values together and as long as at least one of them results in true, the end result will also be “TRUE”.

Next Steps: Adding in the Questions

 “People might not get all they work for in this world, but they must certainly work for all they get”. – Frederick Douglass

At a traditional business, in order for someone to receive a paycheck, there’s a minimum criteria that must be met…they should be in the system (meaning they’re an employee on record), and should have worked a number of hours.  Of course, there’s also the possibility that they took vacation days or sick leave as well, so we’ll look at these three types of “Hours” in order to see if they should be issued a paycheck.

Our conditions (questions) will be:

“Regular Hours”
“Vacation”
“Sick Leave”

Adding these into our “Truth Table”, it becomes:

Looking at the logic of each row in the table:

Row 1: Employee worked “Regular Hours”, took “Vacation”, and submitted “Sick Leave”.  Since at least one of the three was true, the final “Result” is “TRUE”, so the employee will be issued a paycheck.

Row 2: Employee worked “Regular Hours”, took “Vacation”, but did not submit “Sick Leave”.  Again, since at least one of the three was true, the final “Result” is “TRUE”, so the employee will be issued a paycheck.

Rows 3-7: At least one of the three was true, so the final “Result” is “TRUE” which will allow for a paycheck to be issued to the employee.

Row 8: In all previous rows, at least one condition was true, the final result was “TRUE”, but in the last row none of the conditions have been met.  Since none of them result in true, the final “Result” is “FALSE”, so the employee will not be issued a paycheck.

Next time, we’ll look at the “AND” function and how it differs from the “OR”.

- Dessie

Dessie Lunsford
Points-of-Sharing