Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

# Stumped by a nested IF formula based on text-values

Topic Labels: Formulas
Solved
1827 7
cancel
Showing results for
Did you mean:  5 - Automation Enthusiast

Hi all,

I’m currently struggling with a thorny Airtable formula issue. I think I need to be considering using a nested IF statement or a SWITCH formula, but i’m not 100% convinced that either will work. Here’s my problem statement…

I need to analyse the outputs of five columns, each of which is populated by a unique, formula-generated text value. Based on different combinations of these text values, I need to display a different summary sentence.

I thought that I could solve this issue by using the following nested IF statement…

IF({Biz Rules Provided Summary} = “Incomplete”,“Awaiting Business Rules”,
IF({Analysis Complete Summary}) = “Analysis Incomplete”, “Awaiting Analysis”,
IF({Developed Summary}) = “Dev Incomplete”,“Development Incomplete”,
IF({Function Configured}) = “Config Incomplete”,“Awaiting Configuration”,“Function Configured”)))))

However, this formula doesn’t work. It seems to get stuck after the first IF statement and doesn’t process the 2nd, 3rd, 4th & 5th IF statements. This leaves the majority of the column empty.

Can anyone help me out and tell me where i’m going wrong? Should I be using AND’s in the formula somewhere?

Thanks all.

Mark

1 Solution

Accepted Solutions  18 - Pluto

It sounds like you want some combination of all five results to appear in the field at once. Maybe this will do the trick. It puts a newline at the end of each result, so that each result appears on its own line. Here’s the formula:

``````IF({Biz Rules Provided Summary} = "Incomplete","Awaiting Business Rules\n") &
IF({Analysis Complete Summary} = "Analysis Incomplete", "Awaiting Analysis\n") &
IF({Developed Summary} = "Dev Incomplete","Development Incomplete\n") &
IF({Function Configured} = "Config Incomplete","Awaiting Configuration","Function Configured")
``````
7 Replies 7  6 - Interface Innovator

Try this!

IF({Biz Rules Provided Summary} = “Incomplete”,“Awaiting Business Rules”,
IF({Analysis Complete Summary}= “Analysis Incomplete”, “Awaiting Analysis”,
IF({Developed Summary}= “Dev Incomplete”,“Development Incomplete”,
IF({Function Configured} = “Config Incomplete”,“Awaiting Configuration”,“Function Configured”)))))  6 - Interface Innovator

or try this…

IF({Biz Rules Provided Summary} = “Incomplete”,“Awaiting Business Rules”,
IF({Analysis Complete Summary}= “Analysis Incomplete”, “Awaiting Analysis”,
IF({Developed Summary}= “Dev Incomplete”,“Development Incomplete”,
IF({Function Configured} = “Config Incomplete”,“Awaiting Configuration”,0)))))  5 - Automation Enthusiast

Hi @Steve_Vo.

Just tried both formulas…and sadly received this error both times “Sorry, there was a problem saving this field. Invalid formula. Please check your formula text.”

Any ideas?  6 - Interface Innovator

I don’t believe you can do a nested ifs statement for 5 different fields into one. You may have to do 5 of them separately.

example: 1. IF({Biz Rules Provided Summary} = “Incomplete”,“Awaiting Business Rules”)

example 2. IF({Analysis Complete Summary}= “Analysis Incomplete”, “Awaiting Analysis”)

try one on its own and see if it works?  5 - Automation Enthusiast

Hi Steve.

I could certainly make the IF statements separately - i’ve tried this and it works.

However, I need to write a formula that automatically updates other cells when the final one of these statements is true. Each of these statements are steps in a process…and each one supersedes the previous one. Maybe an IF statement isn’t what i’m looking for?  18 - Pluto

It sounds like you want some combination of all five results to appear in the field at once. Maybe this will do the trick. It puts a newline at the end of each result, so that each result appears on its own line. Here’s the formula:

``````IF({Biz Rules Provided Summary} = "Incomplete","Awaiting Business Rules\n") &
IF({Analysis Complete Summary} = "Analysis Incomplete", "Awaiting Analysis\n") &
IF({Developed Summary} = "Dev Incomplete","Development Incomplete\n") &
IF({Function Configured} = "Config Incomplete","Awaiting Configuration","Function Configured")
``````  5 - Automation Enthusiast

Cheers Justin. That worked really well.

Much appreciated.

Best,
Mark 