One of the more common frustrations for users new to working with formulas in SharePoint is the usage of logical functions. Throughout this chapter, my hope is that I can help alleviate some of the confusion in how they work, how to use them, and where they can be of benefit in your lists and libraries by working through a few examples that detail the “logic” behind the formula. Each function has a purpose, and although the logic involved can become confusing at times with complexities needed to build robust solutions, I’m going to break each one down with an explanation of how they all fit together, and why.
The logical functions include the following individual functions:
Returns different values depending on whether a condition specified evaluates to TRUE or FALSE.
Returns a value of TRUE if all arguments in a statement are TRUE. If one or more arguments are FALSE, the entire statement is FALSE.
Returns YES if any argument is TRUE and NO if all arguments are FALSE.
Reverses the value of its argument. Used in cases where you want to test whether a value is not equal to another value.
Returns the logical value TRUE (used primarily for compatibility with other programs; you can also enter the value TRUE directly into your formulas when a requirement exists to test for the TRUE value).
In this chapter, I will be detailing the usage of IF, AND, and OR.
In several of my online articles, I’ve discussed the NOT, TRUE, and FALSE types quite often, but some of the more common questions popping up on forums and blogs daily (and sometimes hourly) are, “How do I write out the logic?” or “What’s the syntax for nesting?” or “Why is it not giving me the answer I wanted?”
The thing to keep in mind when working with these functions is that logic operations are essentially true/false questions. The answer will always be in this form no matter how many nestings there are or cases where you’ll be testing for more than two possible outcomes. In the end, it will always come down to the most basic of terms:
| If the answer to my question is TRUE, do this; if the answer is FALSE, do that. |
It seems rather simple when you look at it like this, but then why is it so troublesome to come up with formulas (sometimes for myself as well) to test for multiple conditions?
If the above is a correct summation (always either true or false), how do we account for checking for multiple (more than two) scenarios? Wouldn’t this make it a multiple choice answer instead of a true/false answer? In human logic terms you may think so, but in computer logic (which is what our calculations are based upon), the answer is no.
Let’s look at a few examples.