How to optimize a complex conditional formula in Airtable?

Other Questions Airtable
1

Hey everyone! I’m kinda new to data formulas and I’m stuck with this Airtable thing. I’m trying to sort survey feedback 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 messy. Here’s a snippet of what I’m dealing with:

IF(AND({UserRating}=4,{SystemRating}=4),'High',
IF(AND({UserRating}=-4,{SystemRating}=-4),'Low',
IF(AND({UserRating}=2,{SystemRating}=2),'Medium',
...
'Default'))))

It goes on for like 25 lines! There’s gotta be a smarter way to do this, right? Any ideas on how to make it shorter and easier to read? I feel like I’m missing something obvious here. Thanks for any help you can give!

2

I’ve encountered similar challenges with complex conditionals in Airtable. A more efficient approach might be to use a SWITCH() function combined with a concatenation of your two rating fields. Here’s a potential solution:

SWITCH(
  {UserRating} & {SystemRating},
  '44', 'High',
  '-4-4', 'Low',
  '22', 'Medium',
  // Add other combinations as needed
  'Default'
)

This method reduces the formula’s length and improves readability. It’s also easier to maintain if you need to add or modify conditions later. Testing is recommended to ensure all scenarios are covered. If more specific groupings are needed, you might consider a separate mapping table with a lookup function.

3

As someone who’s worked extensively with Airtable formulas, I can relate to your struggle. Complex conditionals can quickly become unwieldy. Have you considered using a lookup table approach? Here’s what I’ve found effective:

Create a new table in your base with columns for UserRating, SystemRating, and Result. Fill this table with all possible combinations and their corresponding outcomes.

Then, in your main table, use a VLOOKUP function to reference this lookup table. It might look something like this:

VLOOKUP(UserRating & ‘|’ & SystemRating, ‘LookupTable’, ‘Result’)

This method keeps your main formula clean and makes it much easier to update or modify your classification logic in the future. It’s been a game-changer for me in managing complex conditional scenarios in Airtable.

Just remember to thoroughly test your new setup to ensure all cases are covered correctly.

4

hey, have u tried using a CASE statement? it’s way cleaner than nested IFs. something like:

CASE({UserRating} & {SystemRating}
WHEN ‘44’ THEN ‘High’
WHEN ‘-4-4’ THEN ‘Low’
WHEN ‘22’ THEN ‘Medium’
ELSE ‘Default’
END)

this should make ur formula way shorter and easier 2 read. hope it helps!