Nested IF(AND Formula Question

bhazelton

Trying to display a numerical priority scoring value for task items based on results from 3 input variables: URGENCY, EFFORT, IMPACT values with 1 being the highest value, 2 being the middle value, 3 being the lowest value. For example, if the task is most URGENT=1, most IMPACT=1, least EFFORT=3, that task should be scored with an overall value of "1" and the corresponding highest priority. If the task is least URGENT=3, least IMPACT=3, most EFFORT=1, that task should be scored as a value of "9" and the lowest priority.

I am not receiving #UNPARSEABLE message from formula below, but not getting any value generated either:

=IF(AND(URGENCY@row = "1", EFFORT@row = "3", IMPACT@row = "1"), 1, IF(AND(URGENCY@row = "1", EFFORT@row = "2", IMPACT@row = "1"), 2, IF(AND(URGENCY@row = "2", EFFORT@row = "3", IMPACT@row = "2"), 3, IF(AND(URGENCY@row = "1", EFFORT@row = "1", IMPACT@row = "1"), 4, IF(AND(URGENCY@row = "2", EFFORT@row = "2", IMPACT@row = "2"), 5, IF(AND(URGENCY@row = "2", EFFORT@row = "1", IMPACT@row = "2"), 6, IF(AND(URGENCY@row = "3", EFFORT@row = "3", IMPACT@row = "1"), 7, IF(AND(URGENCY@row = "3", EFFORT@row = "2", IMPACT@row = "3"), 8, IF(AND(URGENCY@row = "3", EFFORT@row = "1", IMPACT@row = "3"), 9)))))))))

Any ideas?

Brent Wilson

I copied and pasted your formula and it works fine

=IF(AND(URGENCY@row = "1", EFFORT@row = "3", IMPACT@row = "1"), 1, IF(AND(URGENCY@row = "1", EFFORT@row = "2", IMPACT@row = "1"), 2, IF(AND(URGENCY@row = "2", EFFORT@row = "3", IMPACT@row = "2"), 3, IF(AND(URGENCY@row = "1", EFFORT@row = "1", IMPACT@row = "1"), 4, IF(AND(URGENCY@row = "2", EFFORT@row = "2", IMPACT@row = "2"), 5, IF(AND(URGENCY@row = "2", EFFORT@row = "1", IMPACT@row = "2"), 6, IF(AND(URGENCY@row = "3", EFFORT@row = "3", IMPACT@row = "1"), 7, IF(AND(URGENCY@row = "3", EFFORT@row = "2", IMPACT@row = "3"), 8, IF(AND(URGENCY@row = "3", EFFORT@row = "1", IMPACT@row = "3"), 9)))))))))

Smartsheet does some weird things with curly brackets that might be it..

That being said

I always like to have a Value if the IF statement fails.

there are 26 Combinations of Data, not 9

Looking at your IF statement there is no value to be assigned for the remaining 18

If a user chooses

URGENCY@row = "3", EFFORT@row = "3", IMPACT@row = "3"

The IF the statement would return a value of BLANK

112,113,122,123,132,133,211,213,221,223,231,311,312,321,322,332,333,333 are all missing values

In Scenarios like these, I usually create something like a Numerical Priority Matrix

And then I do a VLookup from the Data Table to the matrix to determine the Value as Large Nested If's like this are unmanageable

To do the Vlookup I create a "helper" column that I call NPS then your formula simply becomes

=VLOOKUP(NPS@row, {Numerical Priority Matrix}, 6, false)

The other issue that complicates things is using 1,2,3 when you try and create a helper column it tries to add the numerical values not the text so 121 becomes 4 the same as 211 and 112. So I changed it to HIGH MEDIUM LOW.. you could also use symbols