Introduction

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.

This set of formulas is made up of the following functions:

IF – Returns different values depending on whether a condition specified evaluates to TRUE or FALSE.

AND – 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.

OR – Returns YES if any argument is TRUE and NO if all arguments are FALSE.

NOT – Reverses the value of its argument.  Used in cases where you want to test if a value is not equal to another value.

TRUE – Returns the logical value TRUE (used primarily for compatibility with other programs, you can also enter in the value TRUE directly into your formulas when a requirement exists to test for the TRUE value).

FALSE – Same as TRUE, but return FALSE (see above).

In previous articles I’ve discussed these types quite often, but some of the more common questions popping up on forums and blogs daily (and sometimes hourly) is about “How to write out the logic?”, or “What’s the syntax for nesting?”, or “Why is it not giving me the answer I wanted?”

The Basics

The thing to keep in mind when working with these functions is that Logic operations are essentially a “True/False” question.  The answer will always be in this form no matter how many “Nesting’s” 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, and if the answer is “False”, do that.”

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”), then how do we account for checking for multiple (more than two) scenarios?  Wouldn’t this make it a “Multiple Select/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.

“IF” A Question

Let’s look at a few examples to see how to explain this.

“If I’m hungry, I’ll eat.”

Basic human survival, we need to eat to survive.  Am I hungry, yes or no?  If yes, I’ll eat.  If no, I won’t eat. 

This is the most basic of “IF” tests, since it deals with a single question that will have one of two possible answers:

“If hungry = true, result = eat”
“If hungry = false, result = don’t eat”

SharePoint translation:

=IF([Hungry]=”True”,”Eat”, ”Don’t Eat”)

We’ll test this by creating a custom list with two added columns.

First column named “Hungry”, “Choice” type (options are: “True” and “False”), “Radio Buttons” for the type, no default value.

Second column named “Result”, “Calculated” type.  Leave the rest as default and enter in the following formula:

=IF([Hungry]="True","Eat","Don’t Eat")

Once we have our columns, create a new item on the list:

Then view the results:

Since we chose “True” for whether or not we were hungry, the result displayed is “Eat”.

Now go back and edit the item and choose “False” for the answer (notice how it now displays “Don’t Eat”?)

Dissecting the “IF”

The “IF” statement itself, has three basic parts:

1. The logical condition to check for (our question)
2. Result to return if True
3. Result to return if False

In our case, the three pieces are made up of:

1. Condition: Does the value in the “Hungry” column equal “True”?
2. True: Display the result “Eat”
3. False: Display the result “Don’t Eat”

The entire conditional check, or “Question”, can be viewed as:

If the value displayed in the “Hungry” column equals “True”, display the result of “Eat”.  If the value displayed in the “Hungry” column does not equal “True”, display the result of “Don’t Eat”.

Simple and straight to the point – “Yes/No”, “True/False”, “Eat/Don’t Eat”.

“IF” A Second Question

Next, let’s take this one step further and add in a second condition to test for:

“Am I thirsty?”

As before, we look at what our two possible answers for this can be: Am I thirsty, yes or no?  If yes, I’ll drink.  If no, I won’t drink. 

“If thirsty = true, result = drink”
“If thirsty = false, result = don’t drink”

SharePoint translation:

=IF([Thirsty]=”True”,”Drink”, ”Don’t Drink”)

Test this by adding in another column to our list in SharePoint.

Create a column named “Thirsty”, “Choice” type (options are: “True” and “False”), “Radio Buttons” for the type, no default value.

Change the formula in our “Result” column to be:

=IF([Thirsty]="True","Drink","Don’t Drink")

Change the order of the columns in your view so that the “Result” column is last:

“Title”
“Hungry”
“Thirsty”
“Result”

The three pieces of the “IF” statement become:

1. Condition: Does the value in the “Thirsty” column equal “True”?
2. True: Display the result “Drink”
3. False: Display the result “Don’t Drink”

Our “Question” can then be viewed as:

If the value displayed in the “Thirsty” column equals “True”, display the result of “Drink”.  If the value displayed in the “Thirsty” column does not equal “True”, display the result of “Don’t Drink”.

Merging two Formulas

Now that we have our two separate conditions (questions) to test for, how do we put them together in order to test each of them at the same time?

The approach we’ll be taking on this is to “Nest” the formulas together.

In order to nest a series of “IF” statements, let’s look again at the three parts of a basic “IF” formula:

1. The logical condition to check for (our question)
2. Result to return if True
3. Result to return if False

The formula for this is simply:

=IF(condition,True,False)

When we “Nest” an additional “IF” into this, we’re substituting a second instance of the exact same formula in place of either the “True” or “False” in order to check a second condition.

If we want to test for a second condition based on an answer of “True” from our first condition, this becomes:

=IF(condition1,IF(condition2,True,False),False)

This could be read as:

If [condition1] equals “True”, then if [condition2] equals “True”, display the result specified in the [condition2] “True” value.

If [condition1] equals “True”, then if [condition2] does not equal “True”, display the result specified in the [condition2] “False” value.

If [condition1] does not equal “True”, display the result specified in the [condition1] “False” value.

So, even though we’re only looking at two different conditions, we can actually have three different possibilities of answers.

But wait, how can this be possible if (as I stated earlier) a “Logical” test can only have two possible answers of “True” or “False”?

Although it may “Appear” that there are three different answers, we’re still only using two because the second condition is occurring inside the “True” answer for the first condition.

Let’s take a look at the logic of how this is processed by using our test data of “Hungry” and “Thirsty” to illustrate why there are still only two possible answers.

Rewriting our formula becomes:

=IF(Hungry,IF(Thirsty,”Eat and Drink”,”Eat”),”Don’t Eat”)

Written out in English, this becomes:

Is it true that I am hungry?
If so, then is it true that I am thirsty?
If so, I will eat and drink.
If not, I will eat.
If it is not true that I am hungry, I will not eat.

Since the entire conditional check of “Am I thirsty?” takes place in the “True” value of “Am I hungry?” we’re still within the boundaries of a single answer of “True”, so this fits in with the notion of a “Logical” condition having only two possible answers (either “True” or “False”).

Regardless of which of the two sub-answers from the second check we arrive at (True equaling “Eat and Drink” or False equaling “Eat”), the answer is passed back to the original “True” value for the first conditional check, resulting in the “True” value for “Am I hungry” displaying one of the results.

This is how nesting of “IF” statements works.  The first conditional check will only display a single result, whether it’s from itself, or from a nested (child) conditional check, it can only display one result.  The nested (child) conditional check passes its result back to its parent to be displayed, because the parent (first conditional check) will wait until it gets a value to satisfy its condition.

Understandably, this can be somewhat confusing because the logic taking place is sometimes non-intuitive, but if you look at the formula from left-to-right (the same manner in which it is processed) and work through the logic in a manner similar to what I’ve detailed (breaking it down to plain English), hopefully that will help to clear it up.

Let’s look at the formula once again and break down each step from left-to-right:

A “Simplified Expansion”

=IF(Hungry,IF(Thirsty,”Eat and Drink”,”Eat”),”Don’t Eat”)

Starting with each condition equaling “True”:

Step 1-1: look at the formula in its basic form
=IF(Hungry,True,False)

Step 1-2: look at the formula in its basic form with nesting
=IF(Hungry, IF(Thirsty,True,False),False)

Step 1-3: expand the “True” path
IF Hungry = True THEN IF(Thirsty,True,False)

Step 1-4: expand the “True” path of the nested condition
IF Hungry = True THEN IF Thirsty = True, “Eat and Drink”

Step 1-5: pass the values back
IF Hungry = True THEN “Eat and Drink”

Step 1-6: pass the values back until you get to the beginning
“Eat and Drink”

Step 1-7: display the results
Display = “Eat and Drink”

Moving to the next set of answers (first condition equals “True”, second condition equals “False”):

Step 2-1: look at the formula in its basic form
=IF(Hungry,True,False)

Step 2-2: look at the formula in its basic form with nesting
=IF(Hungry, IF(Thirsty,True,False),False)

Step 2-3: expand the “True” path
IF Hungry = True THEN IF(Thirsty,True,False)

Step 2-4: expand the “False” path of the nested condition
IF Hungry = True THEN IF Thirsty = False, “Eat”

Step 2-5: pass the values back
IF Hungry = True THEN “Eat”

Step 2-6: pass the values back until you get to the beginning
“Eat”

Step 2-7: display the results
Display = “Eat”

Moving to the last set of answers (first condition equals “False”):

Step 3-1: look at the formula in its basic form
=IF(Hungry,True,False)

Step 3-2: look at the formula in its basic form with nesting
=IF(Hungry, IF(Thirsty,True,False),False)

Step 3-3: expand the “False” path
IF Hungry = False, “Don’t Eat”

Step 3-4: since we don’t have any further conditions to check for in the “False” path of the first conditional check, pass the values back
“Don’t Eat”

Step 3-5: Display the results
Display = “Don’t Eat”

Once we’ve looked at each path, we can see there are only three possible answers:

1. “Eat and Drink”
2. “Eat”
3. “Don’t Eat”

Since we’re looking at the initial condition of “Am I Hungry?”, and it only has two paths possible (“True” or “False”), this again confirms the notion of only two possible answers for a “Logical” operation (even though there are two values that could be displayed for the “True” answer, only one of them can be chosen and passed back to it’s parent – the first conditional check, where it will be returned as a result).

Putting It All Together

Moving back to SharePoint, let’s see how this looks in our list.

Go back into our “Result” column and modify the formula to the following:

=IF([Hungry],IF([Thirsty],"Eat and Drink","Eat"),"Don’t Eat")

Once saved, go back to the list and enter in a new item choosing “True” for each:

As expected (based on our work-through of the logic earlier) if it is True for both “Hungry” and “Thirsty”, we should see a result of “Eat and Drink” – which we do.

Test out the other various combinations of True and False to see if our logic is correct:

Hungry = True, Thirsty = False:

Hungry = False, Thirsty = True:

Hungry = False, Thirsty = False:

Notice that the last two display the exact same results?  This occurs because in the current form of the calculation, we’re only moving to the second condition if the first condition returns a value of “True” (we’ll look at this further in future articles by discussing how to always process a condition).

Based on the above tests, our logic is working (yay!!) and is successfully processing multiple conditions.

Conclusion

What we’ve looked at it just the “Tip of the Iceberg” when it comes to what you can accomplish through the “IF” function and its nesting capabilities.  In the next article, we’ll be diving in a bit more with deeper nesting’s and introducing an alternative method of checking for multiple conditions (hint – it involves the use of “AND” and “OR”).  Also, we’ll be looking at how to always make sure your parentheses are correct.

Till next time…

- Dessie

Dessie Lunsford
Points-of-Sharing