How to optimize a complex conditional formula in Airtable?

Other Questions Airtable
1

Hey folks! I’m trying to get better at writing formulas in Airtable. I’ve got this survey feedback thing going on where I need to sort responses into six groups based on two scores. Each score can be -4, -2, 0, 2, or 4.

Right now, I’ve got this huge IF/AND formula that works, but it’s super clunky. Here’s a snippet of what I’m dealing with:

IF(AND({Score1}=4,{Score2}=4),'A',
IF(AND({Score1}=-4,{Score2}=-4),'A',
IF(AND({Score1}=2,{Score2}=2),'A',
IF(AND({Score1}=-2,{Score2}=-2),'A',
IF(AND({Score1}=0,{Score2}=0),'B',
...
'A'))))))))))))))))))))))

It goes on like this for a while. I’m sure there’s a smarter way to do this, but I’m kinda stuck. Any ideas on how to make this formula less of a monster? Thanks a bunch for any help!

2

Having worked extensively with Airtable formulas, I’d recommend leveraging the CONCATENATE function combined with a nested IF statement. This approach can significantly streamline your formula while maintaining its functionality.

Here’s a simplified structure:

IF(CONCATENATE({Score1}, {Score2}) = ‘44’, ‘A’,
IF(CONCATENATE({Score1}, {Score2}) = ‘-4-4’, ‘A’,
…)

This method reduces redundancy by combining the scores into a single string for comparison. It’s more manageable and easier to read than multiple AND conditions. You can further optimize by grouping similar outcomes.

I’ve implemented this in several complex survey analysis projects, and it’s proven to be both efficient and maintainable. Give it a try and see if it helps simplify your formula structure.

3

hey flyingleaf, u could try switch() instead. it lets u map conditions cleaner, e.g. switch(1, and(score1=score2, abs(score1)>=2), ‘A’, …). it vastly shortens ur formula so u can focus on logic. give it a shot!

4

As someone who’s dealt with similar scenarios, I’d suggest exploring the ARRAYFORMULA function combined with INDEX and MATCH. This approach can significantly simplify your formula while maintaining flexibility.

Here’s a basic structure:

ARRAYFORMULA(INDEX(‘ResultArray’, MATCH({Score1}&{Score2}, ‘ScoreCombos’, 0)))

‘ResultArray’ would be your A, B, C… outcomes, and ‘ScoreCombos’ would be all possible score combinations.

This method reduces formula complexity, improves performance, and makes future updates much easier. You’ll need to set up your arrays correctly, but once done, it’s incredibly efficient.

I’ve used this in large-scale survey analysis, and it’s been a game-changer. Give it a shot and let us know how it goes!

5

yo FlyingLeaf, have u considered using a lookup table? create a new table with ur score combos and results, then use VLOOKUP to fetch the right group. it’s way cleaner than nested IFs and easier to update later. just my 2 cents!