Like Ogres and Onions – SharePoint Formulas Have Many Layers

To test what we have so far, let’s look at our existing example and build upon it to make it even more complex then use our “Truth Table” and a basic hierarchical flowchart to create our final formula.

First, let’s see what we have and what we want to add to make it more complex.

Our existing formula is:

=IF(Hungry,IF(Thirsty,IF(Tired,"Eat and Drink and Sleep","Eat and Drink"),IF(Tired,"Eat and Sleep","Eat")),IF(Thirsty,IF(Tired,"Drink then Sleep","Drink"),IF(Tired,"Sleep","Don’t Eat or Drink or Sleep")))

With this, we’re checking for three conditions:

“Am I Hungry?”
“Am I Thirsty?”
“Am I Tired?”

To add in a new layer of complexity, we’ll add in a fourth condition of “Am I Sick?”

Building out the first part of our Truth Table, we add in each column needed:

Next, using the pattern we started to see in the last table, add in rows of cells with alternating patterns (the text of “TRUE” and “FALSE”) matching the following sequences:

Question 1: alternate every eight rows
Question 2: alternate every four rows
Question 3: alternate every two rows
Question 4: alternate every other row

When completed, it should appear as:

By entering in the “TRUE / FALSE” values in this predefined pattern, we don’t have to try and figure out how to make sure we cover each and every combination – it just works out.

Each additional condition added will double the total rows: 2 conditions = 4 rows, 3 conditions = 8 rows, 4 conditions = 16 rows, and so on.

Now that we have the table, insert text in the “Result” column for each cell that has a “TRUE” value:

For this we can just use the simple terms of “Eat”, “Drink”, “Sleep”, and “I’m Sick” (adding in “and” in between to make it appear more sentence-like).

Row one (since each column has a “TRUE” value) would be:
“Eat and Drink and Sleep, I’m Sick”

Row two (having “TRUE” in only the first 3 columns) would be:
“Eat and Drink and Sleep”

(Continuing on…) row ten would be:
“Drink and Sleep”

Fill out the rest of the “Results” column with answers following the same pattern and you should come up with:

Now that we have a complete list of all expected results, we can build out a basic flowchart of how the logic should travel (based on these results).

Tomorrow we’ll take a look at the logic of flowcharts…

- Dessie

Dessie Lunsford
Points-of-Sharing