How to optimize nested IF conditions in Airtable formulas?

Other Questions Airtable
1

Seeking advice for simplifying Airtable formula

I’m currently working on analyzing survey responses in Airtable, where I categorize them based on two score inputs. The score values can be -4, -2, 0, 2, or 4, and we have six distinct categories resulting from different score combinations.

My existing approach involves several nested IF statements that function correctly but have become unwieldy and challenging to handle. Here’s the formula I’m using:

IF(AND({Rating A}=4,{Rating B}=4),"Type1",
IF(AND({Rating A}=-4,{Rating B}=-4),"Type1",
IF(AND({Rating A}=2,{Rating B}=2),"Type1",
IF(AND({Rating A}=-2,{Rating B}=-2),"Type1",
IF(AND({Rating A}=0,{Rating B}=0),"Neutral",
IF(AND({Rating A}=4,{Rating B}=2),"Positive",
IF(AND({Rating A}=4,{Rating B}=0),"Positive",
IF(AND({Rating A}=4,{Rating B}=-2),"Positive",
IF(AND({Rating A}=4,{Rating B}=-4),"Strong",
IF(AND({Rating A}=2,{Rating B}=4),"Reverse",
IF(AND({Rating A}=2,{Rating B}=0),"Neutral",
IF(AND({Rating A}=2,{Rating B}=-2),"Neutral",
IF(AND({Rating A}=2,{Rating B}=-4),"Moderate",
IF(AND({Rating A}=0,{Rating B}=4),"Reverse",
IF(AND({Rating A}=0,{Rating B}=2),"Neutral",
IF(AND({Rating A}=0,{Rating B}=-2),"Neutral",
IF(AND({Rating A}=0,{Rating B}=-4),"Moderate",
IF(AND({Rating A}=-2,{Rating B}=4),"Reverse",
IF(AND({Rating A}=-2,{Rating B}=2),"Neutral",
IF(AND({Rating A}=-2,{Rating B}=0),"Neutral",
IF(AND({Rating A}=-4,{Rating B}=4),"Reverse",
IF(AND({Rating A}=-4,{Rating B}=2),"Reverse",
IF(AND({Rating A}=-4,{Rating B}=0),"Reverse",
IF(AND({Rating A}=-4,{Rating B}=-2),"Reverse",
"Type1"))))))))))))))))))))))))

Would you be able to suggest a more efficient way to write this? Perhaps utilizing a SWITCH statement or a different method that improves readability and ease of modification?

2

I encountered a similar issue with performance reviews having multiple rating dimensions. The key is to create a mapping table rather than trying to manage it all internally. Instead of using helper fields or concatenation techniques, leverage a single SWITCH statement based on a calculated total like {Rating A} + {Rating B}. Be mindful of overlapping sums since there are 25 combinations. First, group all “Type1” cases using OR conditions, and then address subsequent categories. This streamlines the formula while clarifying the business logic, making it easier for future analysts to understand and adapt without deciphering complex nested statements.

3

Honestly, just use a vlookup approach with CONCATENATE. Create a text field that joins both ratings like CONCATENATE({Rating A}, "|", {Rating B}) then use nested IF statements to match those strings. Way cleaner than all those AND statements. Something like IF({Combined}="4|4","Type1", IF({Combined}="-4|-4","Type1"... Still nested but at least it’s shorter and easier to debug when it breaks.

4

I’ve dealt with messy formulas like this before - there’s a much cleaner way to handle it. You’re basically doing math on ratings to get categories, right?

Make a helper field that calculates one score:

({Rating A} * 10) + {Rating B}

This gives you unique values like 44, 42, 40, etc. Then just use SWITCH on that helper:

SWITCH({Helper Field},
44, "Type1",
-44, "Type1", 
22, "Type1",
-22, "Type1",
0, "Neutral",
42, "Positive",
40, "Positive",
38, "Positive",
36, "Strong",
24, "Reverse",
20, "Neutral",
18, "Neutral",
16, "Moderate",
4, "Reverse",
2, "Neutral",
-2, "Neutral",
-4, "Moderate",
-16, "Reverse",
-18, "Neutral",
-20, "Neutral",
-36, "Reverse",
-38, "Reverse",
-40, "Reverse",
-42, "Reverse",
"Type1")

This cuts your formula in half and makes changes way easier. Want to add new combinations? Just drop another line into the SWITCH.

5

I’ve had good luck with lookup tables instead of hardcoding everything in formulas. Make a separate table with all your Rating A and Rating B combos, then add a column for the category. In your main table, use CONCATENATE({Rating A}, “-”, {Rating B}) to get strings like “4-4” or “-2-2”, then lookup against your reference table. Way easier to maintain - just edit the lookup table instead of messing with formulas. Handles edge cases better too, and you can see all combinations at once. Also runs faster than nested conditionals on big datasets.