Taming the Elusive “Calculated Column” – Logic – Part 3
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
- Taming the Elusive "Calculated Column"
- EndUserSharePoint.com: Taming the Elusive "Calculated Column" - Customizing a Task List
- EndUserSharePoint.com: Taming the Elusive “Calculated Column” - Customizing a Task List (Part II)
- EndUserSharePoint.com: Taming the Elusive “Calculated Column” - Customizing a Task List (Part III)
- EndUserSharePoint.com: Taming the Elusive “Calculated Column” - Customizing a Task List (Part IV)
- EndUserSharePoint.com: Taming the Elusive “Calculated Column” - Text and Data
- EndUserSharePoint.com: Taming the Elusive "Calculated Column" - Customizing a Contacts List (Part I)
- EndUserSharePoint.com: Taming the Elusive "Calculated Column" - Customizing a Contacts List (Part II-I)
- EndUserSharePoint.com: Taming the Elusive “Calculated Column” - Customizing a Contacts List (Part II-II)
- EndUserSharePoint.com: Taming the Elusive “Calculated Column” - Customizing a Contacts List (Part II-III)
- EndUserSharePoint.com: Taming the Elusive “Calculated Column” – Text and Data (Part III)
- EndUserSharePoint.com: Taming the Elusive “Calculated Column” – Text and Data (Part IV)
- EndUserSharePoint.com: Taming the Elusive “Calculated Column” – Text and Data (Part V)
- EndUserSharePoint.com: Taming the Elusive “Calculated Column” – Text and Data (Part VI)
- Taming the Elusive “Calculated Column” – Lookup and Reference
- Taming the Elusive “Calculated Column” - Logic
- Taming the Elusive “Calculated Column” - Logic - Part 1
- Taming the Elusive “Calculated Column” - Logic - Part 2
- Taming the Elusive “Calculated Column” - Logic - Part 3
- Taming the Elusive “Calculated Column” - Logic - Part 4
- Taming the Elusive “Calculated Column” - Logic - Part 5
- Taming the Elusive “Calculated Column” - Logic - Part 7
- Taming the Elusive “Calculated Column” - Logic - Part 6
- Taming the Elusive “Calculated Column” - Logic - Part 8
- Taming the Elusive “Calculated Column” - Logic - Part 9
- Taming the Elusive “Calculated Column” - Logic - Part 10
- Taming the Elusive “Calculated Column” - Date and Time - Part 1
- Taming the Elusive “Calculated Column” - Date and Time - Part 2
- Taming the Elusive “Calculated Column” - Date and Time - Part 3
- Taming the Elusive “Calculated Column” - Date and Time - Part 4
- Taming the Elusive “Calculated Column” - Referencing Multiple Lines of Text Column
- Taming the Elusive “Calculated Column” – Date and Time – Part 5
- Taming the Elusive “Calculated Column” – Date and Time – Part 6
- Taming the Elusive “Calculated Column” – Date and Time – Part 7
- Taming the Elusive "Calculated Column" - Referencing a Multiple Lines of Text Column With Append Text: Part 1
- Taming the Elusive "Calculated Column" - Referencing a Multiple Lines of Text Column With Append Text: Part 2
- Taming the Elusive "Calculated Column" - Referencing a Multiple Lines of Text Column With Append Text: Part 3