Nested IF(AND Formula Question
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?
Answers

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
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com
Help Article Resources
Categories
Check out the Formula Handbook template!