Three nested if statements - not working

[Troy_Gamble]

I am trying to get a singular text result depending on what date fields have been entered. I can get each one to work separately with the IF(AND statement but I can’t get all three to work as one formula?

Here are the three formulas I have so far:

IF(AND({To Underwriting},{Resubmitted}),“Resubmitted”,“Submitted”)

IF(AND({To Underwriting},{Initial Approval}),“Approved”)

IF(AND({To Underwriting},{Clear to Close}),“Clear to Close”)

Thank you in advance!

[Justin_Barrett]

Because {To Underwriting} is common among all three formulas, you can start by checking for that, then check for the others in turn.

IF(
    {To Underwriting},
    IF(
        {Clear to Close},
        "Clear to Close",
        IF(
            {Initial Approval},
            "Approved",
            IF(
                {Resubmitted},
                "Resubmitted",
                "Submitted"
            )
        )
    )
)

[Troy_Gamble]

Thanks Justin…greatly appreciate the help!

[Charlene_Moss]

I’m try to write the following IF statement in Airtable to eventually make a completion rate chart.

If Date Test Complete is filled in, return 3, If they asked for a deadline extension and the sum of the sections is equal to or greater than 1, return 2, If they asked for a deadline extension but sum of the sections is less than 1 (meaning they didn’t take it), return 1. If they haven’t don’t anything, return 0.

**This is how I’ve attempted writing it. **
IF({Date Test Complete},3, IF({Deadline Extended}, {# test sections}>=1,2,1))

IF({Date Test Complete},3, IF({Deadline Extended},{# test sections}>=1,2, IF({Deadline Extended},{# test sections}<1,1, BLANK())))

Any help would be great! IF statements are not my greatest strength :upside_down_face:

[Justin_Barrett]

Your second version is closer to what it should be…well, what it could be. There are multiple ways to do it, some more optimized/streamlined than others.

Charlene_Moss:

If they haven’t don’t anything, return 0.

Let’s address this first. Any time you want a field to default to 0 or remain empty or anything equivalent to “nothing” if the rest of your comparisons are false, you don’t actually have to put BLANK() as the last part of the IF function. You can just leave it out entirely. So instead of something like:

IF({Date Complete}, "Complete", BLANK())

…all you actually need is:

IF({Date Complete}, "Complete")

This works for any field type. Airtable will determine the proper nothingness to include if nothing else is true.

Next, any time you want an IF function to be true based on multiple comparisons, those comparisons need to be wrapped inside an AND function. AND simply takes any number of comparisons or field checks, and only returns true if all of them are true. Its counterpart, OR, will return true if any single comparison inside it is true.

To use AND (and the BLANK() simplification) on your second formula above, the result would be this:

IF({Date Test Complete},3, IF(AND({Deadline Extended},{# test sections}>=1),2, IF(AND({Deadline Extended},{# test sections}<1),1)))

However, this can be simplified even further. Because both of your second checks look for something in {Deadline Extended}, you can do a single check for that first, and only test the value in {# of test sections} if the deadline has been extended. And once that value has been tested to see if it’s >= 1, there’s no need for a second check to see if it’s < 1, so we end up with something closer to your first attempt:

IF({Date Test Complete},3, IF({Deadline Extended}, IF({# test sections}>=1,2,1)))

[Charlene_Moss]

Thank you so much Justin! This was super helpful and now I can properly make my chart. Your help is greatly appreciated! :grinning: