How can I simplify nested IF statements in my Airtable formula?

Other Questions Airtable
1

I’m seeking advice on how to improve a complicated formula I’m using in Airtable. I’m analyzing survey feedback and have two score fields where values can be -4, -2, 0, 2, or 4. I need to categorize feedback responses into one of six groups based on these scores (Q, I, A, P, R, M).

Currently, I’ve written a formula with a lot of nested IF statements, which technically works but is quite convoluted:

IF(AND({Score1}=4,{Score2}=4),"Q",
IF(AND({Score1}=-4,{Score2}=-4),"Q",
IF(AND({Score1}=2,{Score2}=2),"Q",
IF(AND({Score1}=-2,{Score2}=-2),"Q",
IF(AND({Score1}=0,{Score2}=0),"I",
IF(AND({Score1}=4,{Score2}=2),"A",
IF(AND({Score1}=4,{Score2}=0),"A",
IF(AND({Score1}=4,{Score2}=-2),"A",
IF(AND({Score1}=4,{Score2}=-4),"P",
IF(AND({Score1}=2,{Score2}=4),"R",
IF(AND({Score1}=2,{Score2}=0),"I",
IF(AND({Score1}=2,{Score2}=-2),"I",
IF(AND({Score1}=2,{Score2}=-4),"M",
IF(AND({Score1}=0,{Score2}=4),"R",
IF(AND({Score1}=0,{Score2}=2),"I",
IF(AND({Score1}=0,{Score2}=-2),"I",
IF(AND({Score1}=0,{Score2}=-4),"M",
IF(AND({Score1}=-2,{Score2}=4),"R",
IF(AND({Score1}=-2,{Score2}=2),"I",
IF(AND({Score1}=-2,{Score2}=0),"I",
IF(AND({Score1}=-4,{Score2}=4),"R",
IF(AND({Score1}=-4,{Score2}=2),"R",
IF(AND({Score1}=-4,{Score2}=0),"R",
IF(AND({Score1}=-4,{Score2}=-2),"R",
"Q"))))))))))))))))))))))))

Is there a cleaner way to rewrite this formula? Perhaps by using SWITCH or another method?

2

try using CONCATENATE with SWITCH instead - something like SWITCH(CONCATENATE({Score1},",",{Score2}),"4,4","Q","2,2","Q"...) way cleaner than all those nested ifs. creates a single string key for each combo then matches it. much easier to read and maintain too