Using the method of building out a “Truth Table” then a “Flowchart” for each and every nested formula may not always be the best approach for you given the time it takes to build them out, but as the complexity of your formulas increase, you might find that having them can decrease the “headache-time” of debugging and troubleshooting when your logic goes awry.

You now have a complete formula that includes every step of the logic needed to ask each question in a manner that allows for each possible answer

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. 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).

This is part of what makes Truth Tables such a valuable tool when prototyping or working-out the logic beforehand…they seem a bit daunting at first, but once you figure out the pattern, you can write them rather quickly then translate it into a basic flowchart to see the path the logic needs to process through. Once you have this, you will also have the basics or “Root” of the formula you need to build to move into SharePoint.

Therein lays one of the inherent difficulties when working with nested operations. In order to account for each possible scenario, you must add in additional logical checks for each, which tends to make your formulas expand immensely (more on this later) with each additional question.

Taming the Elusive “Calculated Column” Logic. In this article I want to discuss one of the more common causes of frustration when users develop formulas – “Logical Functions”, and hopefully shed some light on the “How’s and “Why’s” of their inner-workings.