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"
)
)
)
)

Thanks Justin…greatly appreciate the help!
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"
)
)
)
)

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
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
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.
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)))
Thank you so much Justin! This was super helpful and now I can properly make my chart. Your help is greatly appreciated!