I’d be happy to provide more information!
I’ve spent a lot of time in the Stack Overflow forums. Many of the great pieces of knowledge found there is from many years ago. With that in mind, I’m a big believer in the benefits of building a huge knowledge base for users in the coming years.
Just some context as to the amount that I might write.
Here’s the full breakdown:
- Refresher On The IF Function
-
The Actual Formula
Quick refresher on the IF function
The syntax of the IF function is:
IF( a, b, *c)
The c statement is marked since it is technically optional.
More granularly, the IF function takes statement a, and if that statement returns a truthy, then the b statement will be evaluated.
Example:
- An IF function with only an
a & b statement
IF(
{My Name} = "Ben.Young",
"Yes"
)
Here, we’re evaluating the statement of {My Name} = "Ben.Young".
This is the equivalent of: Does this field match this specific string?
If that statement returns true, then the formula will return a value of Yes.
Now, what if the statement returns false?
In that situation, it wouldn’t return anything. It would just return blank.
- An IF function with all three statements
IF(
{My Name} = "Ben.Young",
"Yes",
"No"
)
Here, we’re dealing with the same formula, except we have added the c statement.
In this case, it’s No.
So, the same behavior occurs if the a statement returns a truthy.
The only difference is that if the a statement returns false, the c statement will be returned. In this case, it will return No.
Our Formula
The formula I gave you has three different IF functions.
IF(
{College(s)},
{College(s)}
& IF(
{Department(s)},
" and " & {Department(s)}
),
IF(
{Department(s)},
{Department(s)}
)
)
Let’s break it down.
When dealing with code or formulas, I find that the rubber duck method works best for me.
In short, I always try to explain what I’m trying to do to myself.
Since it’s a story, we always start from the beginning.
IF(
{College(s)},
The Condition
Now, it doesn’t really matter what type of truthy your statement returns. As long as your statement returns one, it will always work.
The condition in our formula looks a bit weird since it doesn’t really seem to evaluate anything against a condition like this example:
(5 + 5) = 10 → Returns True.
(5 + 5) = 15 → Returns False.
Our condition is simply:
{College(s)},
So why does this work as a condition to evaluate?
It works because we’re testing whether or not there is a value at all.
The presence of data is a truthy.
If {College(s)} is empty, then we get a false.
If {College(s)} has any data, then we will always get a true.
It’s Telling The Truth!
Now that we’ve discovered that {College(s)} has data in it (and is returning true as a result), the IF function will look to the b part of the function to evaluate.
Remember that the syntax is IF(a, b, *c).
Let’s add a new chapter to our story:
IF(
{College(s)},
{College(s)}
& IF(
{Department(s)},
" and " & {Department(s)}
),
To understand this chapter, let’s consider what we’re trying to do (our rubber duck comes in handy here!)
We want to have data return like this:
College 1 and Department 2
Knowing what we want to happen, we can start building.
First, we add in the {College(s)} field. We don’t need to use another IF function since we wouldn’t even be in this chapter unless the {College(s) field wasn’t empty.
After this, we need to make a decision in the story.
What do we do if the {Department(s)} field is empty?
This is where we can insert another IF function.
When functions live inside other functions, they’re referred to as nested.
We understand enough about the IF function now to be able to quickly add this component.
IF(
{Department(s)},
" and " & {Department(s)}
)
If the {Department(s)} field returns true, then we will get the department value along with the " and ".
What If It Failed The Test?
Now that we know what happens if the condition is met, we can define what happens if the condition is not met. This is the optional c parameter of the function.
With the ‘false statement’ added, our formula now looks like this:
IF(
{College(s)},
{College(s)}
& IF(
{Department(s)},
" and " & {Department(s)}
),
IF(
{Department(s)},
{Department(s)}
)
)
If you look closely, you’ll notice that we’re only nesting a simple IF function within the formula.
IF(
{Department(s)},
{Department(s)}
)
While the function is simple, it’s important to consider why it exists.
Start from the beginning of the story, and you’ll see that we’re testing for the existence of the {College(s)} value.
We know what happens if the condition is met.
But if the condition is not met, then we arrive at this last test.
If the {College(s)} field does not have a value, then we will evaluate whether the {Department(s)} field has a value.
If it does, then we’ll display it, but if not, then nothing will happen since we did not define a c (fail) parameter to nested IF function.
