Help

Stumped by a nested IF formula based on text-values

Topic Labels: Formulas
Solved
Jump to Solution
3930 7
cancel
Showing results for 
Search instead for 
Did you mean: 
Mark_Kenny
5 - Automation Enthusiast
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({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

1 Solution

Accepted Solutions

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.

Screen Shot 2019-07-15 at 9.29.31 AM.png

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

See Solution in Thread

7 Replies 7
Steve_Vo
6 - Interface Innovator
6 - Interface Innovator

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
6 - Interface Innovator
6 - Interface Innovator

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
5 - Automation Enthusiast
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?

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?

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?

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.

Screen Shot 2019-07-15 at 9.29.31 AM.png

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

Hi @Justin_Barrett.

Cheers Justin. That worked really well.

Much appreciated.

Best,
Mark