I'm pretty new to writing formulas to manipulate data. I'm using Airtable to gather survey feedback and assign each response a category based on 2 scores provided. Each or two scores can be -4, -2, 0, 2, or 4. Depending on the combination of scores, the response will go into one of six categories.
To do this, I've written an If/And formula (that works), but I'm wondering if there's a simpler and cleaner way to achieve this:
IF(AND({Functional score}=4,{Dysfunctional score}=4),"Q",
IF(AND({Functional score}=-4,{Dysfunctional score}=-4),"Q",
IF(AND({Functional score}=2,{Dysfunctional score}=2),"Q",
IF(AND({Functional score}=-2,{Dysfunctional score}=-2),"Q",
IF(AND({Functional score}=0,{Dysfunctional score}=0),"I",
IF(AND({Functional score}=4,{Dysfunctional score}=2),"A",
IF(AND({Functional score}=4,{Dysfunctional score}=0),"A",
IF(AND({Functional score}=4,{Dysfunctional score}=-2),"A",
IF(AND({Functional score}=4,{Dysfunctional score}=-4),"P",
IF(AND({Functional score}=2,{Dysfunctional score}=4),"R",
IF(AND({Functional score}=2,{Dysfunctional score}=0),"I",
IF(AND({Functional score}=2,{Dysfunctional score}=-2),"I",
IF(AND({Functional score}=2,{Dysfunctional score}=-4),"M",
IF(AND({Functional score}=0,{Dysfunctional score}=4),"R",
IF(AND({Functional score}=0,{Dysfunctional score}=2),"I",
IF(AND({Functional score}=0,{Dysfunctional score}=-2),"I",
IF(AND({Functional score}=0,{Dysfunctional score}=-4),"M",
IF(AND({Functional score}=-2,{Dysfunctional score}=4),"R",
IF(AND({Functional score}=-2,{Dysfunctional score}=2),"I",
IF(AND({Functional score}=-2,{Dysfunctional score}=0),"I",
IF(AND({Functional score}=-4,{Dysfunctional score}=4),"R",
IF(AND({Functional score}=-4,{Dysfunctional score}=2),"R",
IF(AND({Functional score}=-4,{Dysfunctional score}=0),"R",
IF(AND({Functional score}=-4,{Dysfunctional score}=-2),"R",
"Q"))))))))))))))))))))))))
Thank you for your help!
Aucun commentaire:
Enregistrer un commentaire