How to optimize nested IF conditions in Airtable formulas?

Other Questions Airtable
1

I’m working on an Airtable database to categorize customer feedback based on two rating values. Each rating can be -4, -2, 0, 2, or 4, and I need to sort responses into six different groups depending on these score combinations.

My current formula works but it’s really long and messy. Is there a better way to write this?

IF(AND({Rating_A}=4,{Rating_B}=4),"Group_Q",
IF(AND({Rating_A}=-4,{Rating_B}=-4),"Group_Q",
IF(AND({Rating_A}=2,{Rating_B}=2),"Group_Q",
IF(AND({Rating_A}=-2,{Rating_B}=-2),"Group_Q",
IF(AND({Rating_A}=0,{Rating_B}=0),"Group_I",
IF(AND({Rating_A}=4,{Rating_B}=2),"Group_A",
IF(AND({Rating_A}=4,{Rating_B}=0),"Group_A",
IF(AND({Rating_A}=4,{Rating_B}=-2),"Group_A",
IF(AND({Rating_A}=4,{Rating_B}=-4),"Group_P",
IF(AND({Rating_A}=2,{Rating_B}=4),"Group_R",
IF(AND({Rating_A}=2,{Rating_B}=0),"Group_I",
IF(AND({Rating_A}=2,{Rating_B}=-2),"Group_I",
IF(AND({Rating_A}=2,{Rating_B}=-4),"Group_M",
IF(AND({Rating_A}=0,{Rating_B}=4),"Group_R",
IF(AND({Rating_A}=0,{Rating_B}=2),"Group_I",
IF(AND({Rating_A}=0,{Rating_B}=-2),"Group_I",
IF(AND({Rating_A}=0,{Rating_B}=-4),"Group_M",
IF(AND({Rating_A}=-2,{Rating_B}=4),"Group_R",
IF(AND({Rating_A}=-2,{Rating_B}=2),"Group_I",
IF(AND({Rating_A}=-2,{Rating_B}=0),"Group_I",
IF(AND({Rating_A}=-4,{Rating_B}=4),"Group_R",
IF(AND({Rating_A}=-4,{Rating_B}=2),"Group_R",
IF(AND({Rating_A}=-4,{Rating_B}=0),"Group_R",
IF(AND({Rating_A}=-4,{Rating_B}=-2),"Group_R",
"Group_Q")))))))))))))))))))))))))

Any suggestions would be great!

2

Yeah that formula is brutal to debug. I’ve been there with similar rating systems.

Here’s what worked for me - create a lookup table in another base or use a simpler scoring system. But if you’re stuck with this exact setup, try grouping by the actual logic patterns instead of individual combinations.

Looking at your data, you can simplify this way:

IF(AND({Rating_A}={Rating_B}, OR({Rating_A}=4,{Rating_A}=2,{Rating_A}=-2,{Rating_A}=-4)), "Group_Q",
IF(AND({Rating_A}={Rating_B}, {Rating_A}=0), "Group_I",
IF({Rating_A}=4, IF({Rating_B}=-4,"Group_P","Group_A"),
IF({Rating_A}=-4, "Group_R",
IF(OR(AND({Rating_A}=2,{Rating_B}=-4),AND({Rating_A}=0,{Rating_B}=-4)), "Group_M",
IF({Rating_A}=2, IF({Rating_B}=4,"Group_R","Group_I"),
"Group_I"))))))

This catches the patterns first - like when both ratings match, or when Rating_A is -4 (always Group_R). Then handles the exceptions.

Way shorter and when someone asks you to explain the logic six months from now, you won’t hate yourself.

3

Try creating helper fields that calculate the math relationships between your ratings first. Your grouping logic follows patterns based on rating differences and sums, not individual combinations. Make one field with {Rating_A} + {Rating_B} and another with ABS({Rating_A} - {Rating_B}), then use those in simpler SWITCH statements. This cuts down redundancy since many of your conditions end up in the same groups anyway. I’ve done this with complex scoring matrices - makes troubleshooting way easier later.

4

I had the same issue building project priority matrices. Skip the concatenation and helper fields - use nested SWITCH statements instead. Put Rating_A as your outer condition and Rating_B inside: SWITCH({Rating_A}, 4, SWITCH({Rating_B}, 4, "Group_Q", 2, "Group_A", 0, "Group_A", -2, "Group_A", -4, "Group_P"), 2, SWITCH({Rating_B}, 4, "Group_R", 2, "Group_Q"...)). Way cleaner than string manipulation and easier to read. When you need to tweak specific combinations later, you’ll find them instantly without hunting through concatenated mess. Works better with large datasets too.

5

that’s a massive formula! i’d use CONCATENATE to merge both ratings into one string first, then swap those nested IFs for SWITCH. something like SWITCH(CONCATENATE({Rating_A},{Rating_B}),"44","Group_Q","-4-4","Group_Q"...) much cleaner and way easier to maintain.