Stumped by a nested IF formula based on text-values

[Mark_Kenny]

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({Screen Designed Summary}) = “Design Incomplete”,“Awaiting Screen Design”,
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

[Steve_Vo]

Try this!

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

[Steve_Vo]

or try this…

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

[Mark_Kenny]

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?

[Steve_Vo]

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?

[Mark_Kenny]

Hi Steve.

Thanks for your responses.

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?

[Justin_Barrett · Accepted solution]

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({Screen Designed Summary} = "Design Incomplete","Awaiting Screen Design\n") &
IF({Developed Summary} = "Dev Incomplete","Development Incomplete\n") &
IF({Function Configured} = "Config Incomplete","Awaiting Configuration","Function Configured")

[Mark_Kenny]

Hi @Justin_Barrett.

Cheers Justin. That worked really well.

Much appreciated.

Best,
Mark