IF formula only working on first of 3 nested "IF" statements

[Cole_Wedemeier]

I currently have the following IF nested statement. I don’t get an error when saving, but it only works when the first IF is found (13) and produces nothing for a result when the other two are in the identified field to check.

Am I missing a key element to make the “false” portions of each IF statement work with a new nested IF statement?

IF({⛔ Extender Weeks}=13, SUM({👌 WEEKS in Program (26 = 6mo)}+13, IF({⛔ Extender Weeks}=9, SUM({👌 WEEKS in Program (26 = 6mo)}+9, SUM({👌 WEEKS in Program (26 = 6mo)}+4)))))

[Ben_Young1]

Hey @Cole_Wedemeier!

Here is my revised version of your formula.

IF(
    AND(
        {⛔ Extender Weeks},
        {👌 WEEKS in Program (26 = 6mo)}
    ),
    IF(
        {⛔ Extender Weeks} = 13,
        {👌 WEEKS in Program (26 = 6mo)} + 13,
        IF(
            {⛔ Extender Weeks} = 9,
            {👌 WEEKS in Program (26 = 6mo)} + 9
        )
    ),
    "⚠ Missing Key Data!"
)

Here’s how it works…

1. Both the Extender Week and Weeks in Program fields must have data in them in order for the formula to work.

2. If both fields have data in them, then the following will happen:

   • If Extender Weeks = 13, then add 13 to Weeks in Program.
   • If Extender Weeks != 13, then evaluate if Extender Weeks = 9
     • If Extender Weeks = 9, then add 9 to Weeks in Program.

3. If both fields are empty or return a falsey value, then a ⚠ Missing Key Data! message will return. You can edit that message or remove it if you’d like.

If you’re curious about what I changed and why it behaves the way it does, or why your original formula wasn’t working as intended, please feel free to fire off a reply and I’d be happy to explain it in-depth.
The same is true for any general questions or help!

[Cole_Wedemeier]

@Ben.Young

Wonderful! If I wanted to take that to the last level that I originally had, where the 3rd level of my original formula was that if the first two IF statements were both FALSE (as in it didn’t equal 13 or 9 for Extender Weeks) then it should do sum of Weeks in Program + 4 what does that look like when working with the AND added in for the very start?

Thank you so much.

[Ben_Young1 · Accepted solution]

Oooo…

I don’t know how I missed that last portion of the formula!

If you wanted to add the last layer to account for the + 4 weeks, then it would look like this:

IF(
    AND(
        {⛔ Extender Weeks},
        {👌 WEEKS in Program (26 = 6mo)}
    ),
    IF(
        {⛔ Extender Weeks} = 13,
        {👌 WEEKS in Program (26 = 6mo)} + 13,
        IF(
            {⛔ Extender Weeks} = 9,
            {👌 WEEKS in Program (26 = 6mo)} + 9,
            IF(
                {⛔ Extender Weeks} = 4,
                {👌 WEEKS in Program (26 = 6mo)} + 4
            )
        )
    ),
    "⚠ Missing Key Data!"
)

Now, regarding the AND function as the conditional, nothing really changes beyond the fact that we’ve just added a new layer to the IF nesting.
It’s possible that I’ve misread your question about the AND statement, so please let me know if it went over my head.

Functionally, the formula will now just continue up the layers of 13, 9, and 4.

With that being said, I had something cross my mind when I was previously working on the original post.

---

Enter… My ADHD!

(I want to clarify that I tend to write things out like this because they tend to be extremely valuable for people that might go searching for help when they run into problems. So apologies if I seem like I presume you don’t know what’s happening.)

Are you using a single select field for the Extender Weeks field? Or are you using a text field?

If you’re using a single select field, then you don’t have to worry about scenarios where the Extender Weeks field will have any other value besides those three options.

However, if you’re using a text/number field, I had a slight concern about the behavior of the formula.

---

Now here’s a scenario…

Both fields (Extender Weeks & Weeks In) are both filled in.
However, let’s say that Extender Weeks, despite being filled in, doesn’t have any of 13,9, or 4.

If this happens, the formula will just return null/blank.

This happens because the formula executed the truthy block, which is the nested IF statements.
But if none of the nested IF statements return true, then at the end of the last IF (the one that checks if it equals 4), the formula does nothing because we haven’t defined any actions for falsey conditions.

The syntax is written as:

IF(
   {Condition 1},
   {Action if Truthy},
   {Action if Falsey}
)

But something about how IF statements generally work, is that the Falsey block is actually optional.

You can think of this as…

IF(
   {Condition 1},
   {Action if Truthy}
)

In this example, if the condition returns falsey, then it will just return nothing.
If you look at the formula I provided you and pull apart the first action, you have three IF statements.
Each of them has a condition and an action if the conditions returns true.

However, none of them have a falsey block, so if Extender Weeks = 2, then it will just return blank.
So, let’s say you want to create some sort of message that lets you know something ain’t right.
In order to do this, you can use this formula structure:

1  IF(
2      AND(
3        {⛔ Extender Weeks},
4        {👌 WEEKS in Program (26 = 6mo)}
5    ),
6    IF(
7        {⛔ Extender Weeks} = 13,
8        {👌 WEEKS in Program (26 = 6mo)} + 13,
9        IF(
10            {⛔ Extender Weeks} = 9,
11            {👌 WEEKS in Program (26 = 6mo)} + 9,
12            IF(
13                {⛔ Extender Weeks} = 4,
14                {👌 WEEKS in Program (26 = 6mo)} + 4,
15                "Example Message!"
16            )
17        )
18    ),
19    "⚠ Missing Key Data!"
20 )

Everything is the same here, except for that very last IF statement.
Here it is without all the noise:

IF(
   {⛔ Extender Weeks} = 4,
   { 👌 WEEKS in Program (26=6mo)} + 4,
   "Example Message!"
)

Here, if Extender Weeks isn’t 4, then the formula will return the Example Message! text.

It looks like this:

---

Just food for thought and me being bored.
Again, let me know if you have any questions or think I’m insane and scratching at the walls.

[Cole_Wedemeier]

@Ben.Young I LOVE this full-out explanation!

So the “Extender Weeks” is a # field that is populated on zapier automation and a “stop sign” icon to indicate to the team in the company to NOT TOUCH :slightly_smiling_face: They’re pretty great about that too.

However, I do like the addition of the message as a just-in-case.

I applied your updated formula to account for the +4, but now, every single record populates with your “ :warning: Missing Key Data!” message at the end of the formula. If I take that last bit out, I get nothing returned for results in the field at all. :thinking:

[Ben_Young1]

@Cole_Wedemeier - Here’s a viewing link to the base I’ve been experimenting with.

Feel free to copy the base and play with the behavior.
I added a quick description app that also holds the formula as well.

---

Airtable

Extender Weeks & IF Statements - Airtable

Explore the "Extender Weeks & IF Statements" base on Airtable.

My recommendation in situations like this is to delete the formula field you’ve been working with previously.

Go through the formula I provided and make sure that all the field names are 100% accurate to what’s in your base.

---

Formulas are weird.
Airtable can be weird.

Put the two together and you get something that’s just… strange.

[Cole_Wedemeier]

@Ben.Young

Ah! It was a matter of having the row #'s in the quote copied in. Thank you so much! Truly appreciate all of your thought-out explanations and assistance.