Is there a way to optimize a complex conditional formula in Airtable?

Other Questions Airtable
1

Hey folks! I’m trying to categorize survey responses in Airtable based on two scores. Each score can be -4, -2, 0, 2, or 4. I’ve got a formula that works but it’s super long and messy. Here’s what I’ve come up with:

IF(AND({Impact score}=4,{Satisfaction score}=4),"H",
IF(AND({Impact score}=-4,{Satisfaction score}=-4),"H",
IF(AND({Impact score}=2,{Satisfaction score}=2),"H",
...
IF(AND({Impact score}=-4,{Satisfaction score}=-2),"L",
"H"))))))))))))))))))))

It goes on for about 25 lines! There’s gotta be a better way to do this right? I’m pretty new to formulas so any tips would be awesome. Thanks!

2

hey, i’ve dealt with similar stuff before. have u tried using a SWITCH() function? it can simplify complex conditionals a lot. something like SWITCH(1, AND({Impact score}=4, {Satisfaction score}=4), ‘H’, …) might work. could make ur formula way shorter and easier to read!

3

I’ve encountered similar challenges with complex conditionals in Airtable. One approach that’s worked well for me is creating a lookup table in a separate sheet. You can list all possible combinations of Impact and Satisfaction scores, along with their corresponding category (H or L). Then, use a VLOOKUP or ARRAYJOIN function to fetch the category based on the scores. This method keeps your main formula clean and makes it easier to update categorizations if needed. It also improves performance for large datasets. Just ensure your lookup table covers all possible combinations to avoid errors.

4

I’ve been in your shoes, and I get how frustrating those lengthy formulas can be. What worked wonders for me was using a combination of AND() and OR() functions to group similar conditions. Something like:

OR(
AND({Impact score} = 4, {Satisfaction score} = 4),
AND({Impact score} = -4, {Satisfaction score} = -4),
AND(ABS({Impact score}) = 2, ABS({Satisfaction score}) = 2)
)

This approach significantly cut down my formula size. You could then wrap this in an IF statement to assign ‘H’ or ‘L’. It’s not only more compact but also easier to maintain and debug. Just be careful with the logic to ensure all your conditions are covered correctly.