Convert High, Medium, Low to numbers (without nested IF statements)?

Mike.A

For each row in a sheet, I need to convert High/Medium/Low IMPACT and EFFORT text into numeric scores like the table below.

I know I can do this with nested IF statements, but was able to do this much more simply in google sheets using VLOOKUP or CHOOSE functions:

=VLOOKUP(M4,{"Low",1;"Medium",2;"High",2},2,FALSE) / VLOOKUP(N4,{"Low",1;"Medium",2;"High",2},2,FALSE)

=CHOOSE(MATCH(M4,{"Low","Medium","High"},0),1,2,3) / CHOOSE(MATCH(N4,{"Low","Medium","High"},0),1,2,3)

I cannot find a CHOOSE function in smartsheets, and the VLookup does not seem to let me replace the "range" reference with and array (like {"Low",1;"Medium",2;"High",2}).

Does anyone know if this can be done in smartsheets, or am I stuck with the following more complicated nested IF? Advice appreciated!

=IF(AND([OBJ: IMPACT]@row = "High", [OBJ: EFFORT]@row = "High"), 1, IF(AND([OBJ: IMPACT]@row = "Medium", [OBJ: EFFORT]@row = "High"), 2, IF(AND([OBJ: IMPACT]@row = "Low", [OBJ: EFFORT]@row = "High"), 3, IF(AND([OBJ: IMPACT]@row = "High", [OBJ: EFFORT]@row = "Medium"), 2, IF(AND([OBJ: IMPACT]@row = "Medium", [OBJ: EFFORT]@row = "Medium"), 3, IF(AND([OBJ: IMPACT]@row = "Low", [OBJ: EFFORT]@row = "Medium"), 4, IF(AND([OBJ: IMPACT]@row = "High", [OBJ: EFFORT]@row = "Low"), 3, IF(AND([OBJ: IMPACT]@row = "Medium", [OBJ: EFFORT]@row = "Low"), 4, IF(AND([OBJ: IMPACT]@row = "Low", [OBJ: EFFORT]@row = "Low"), 5)))))))))

Mark Cronk

Hi @Mike.A ,

That's a beautiful nested IF statement and I think it's your best solution in Smartsheets. You could simplify the formula by using helper columns to do some of the calculations outside the cell formula. I don't think that gains you any real advantage though.

Lots of really smart people in the Community. Maybe someone has a creative idea for you.

Happy New Year.

Mark

Ben Goldblatt

Hi @Mike.A,

I agree that your nested IF formula may be the way to go here and I highly recommend Submitting a Product Enhancement Request to see about including a CHOOSE function in a future release.

You may be able to get the formula results you're looking for using a combination of the INDEX, COLLECT, and JOIN functions but this would require some "helper" columns (as Mark pointed out) to list out the number values and to join together the IMPACT and EFFORT values. Here's an example of what this would look like, going off of the logic used in your IF statement:

So the number values are listed out in the "Help Table" section of my sheet's Primary Column and I'm using a column-level formula to join the IMPACT and EFFORT values together in my "JOIN" column.

• =JOIN([OBJ: IMPACT]@row:[OBJ: EFFORT]@row, " ")

In the "Data Rows" section of the sheet, if I change either the IMPACT or the EFFORT value, the JOIN value on the row will update and the number will populate accordingly. I'm using the following formulas in rows 12 through 20 in my "Formula results" column:

• =INDEX(COLLECT([Primary Column]2:[Primary Column]10, JOIN2:JOIN10, JOIN@row), 1)
• =INDEX(COLLECT([Primary Column]3:[Primary Column]11, JOIN3:JOIN11, JOIN@row), 1)
• =INDEX(COLLECT([Primary Column]4:[Primary Column]12, JOIN4:JOIN12, JOIN@row), 1)
• =INDEX(COLLECT([Primary Column]5:[Primary Column]13, JOIN5:JOIN13, JOIN@row), 1)
• =INDEX(COLLECT([Primary Column]6:[Primary Column]14, JOIN6:JOIN14, JOIN@row), 1)
• =INDEX(COLLECT([Primary Column]7:[Primary Column]15, JOIN7:JOIN15, JOIN@row), 1)
• =INDEX(COLLECT([Primary Column]8:[Primary Column]16, JOIN8:JOIN16, JOIN@row), 1)
• =INDEX(COLLECT([Primary Column]9:[Primary Column]17, JOIN9:JOIN17, JOIN@row), 1)
• =INDEX(COLLECT([Primary Column]10:[Primary Column]18, JOIN10:JOIN18, JOIN@row), 1)

Feel free to give this a try to see if this will work with what you have set up.

I hope this helps!

Thanks,

Ben

Mike.A

Thanks @Ben G and @Mark Cronk. Interesting and creative ideas. Nice to have the community help. I was trying to avoid helper columns if possible as it just adds more dependencies just to do the calculation. For now will stick with the nested IFs.

The thing google does better here (that allows a lot of simplification) is not just having functions like CHOOSE, but more importantly, allowing a list of values to be used instead of a range in the VLookup (and other functions).

{"Low",1;"Medium",2;"High",3}

I will find some time to submit an enhancement request and see what happens!

Mike.A

Enhancement Request submitted: 2020-12-31.

Smartsheet Product Enhancement Requests

Category Formulas Enhancement Request

Title: Allow use of value list (array) in place of Range arrays in functions like VLOOKUP

Enhancement Request Description

For each row in a sheet, I need to convert High/Medium/Low IMPACT and EFFORT text into numeric scores (see attached table).

I know I can do this with extremely complicated nested IF statements or helper columns, but in Google sheets I am was able to do this much more simply using VLOOKUP or CHOOSE functions. The key difference is google allows you to replace the lookup Range reference with an array of values, making it very simple to translate text like "High/Med/Low" to numbers.

GOOGLE FORMULA

=VLOOKUP(M4,{"Low",1;"Medium",2;"High",3},2,FALSE) / VLOOKUP(N4,{"Low",1;"Medium",2;"High",3},2,FALSE)

=CHOOSE(MATCH(M4,{"Low","Medium","High"},0),1,2,3) / CHOOSE(MATCH(N4,{"Low","Medium","High"},0),1,2,3)

SMARTSHEET NESTED IF (much more complicated and error prone).

=IF(AND([OBJ: IMPACT]@row = "High", [OBJ: EFFORT]@row = "High"), 1, IF(AND([OBJ: IMPACT]@row = "Medium", [OBJ: EFFORT]@row = "High"), 2, IF(AND([OBJ: IMPACT]@row = "Low", [OBJ: EFFORT]@row = "High"), 3, IF(AND([OBJ: IMPACT]@row = "High", [OBJ: EFFORT]@row = "Medium"), 2, IF(AND([OBJ: IMPACT]@row = "Medium", [OBJ: EFFORT]@row = "Medium"), 3, IF(AND([OBJ: IMPACT]@row = "Low", [OBJ: EFFORT]@row = "Medium"), 4, IF(AND([OBJ: IMPACT]@row = "High", [OBJ: EFFORT]@row = "Low"), 3, IF(AND([OBJ: IMPACT]@row = "Medium", [OBJ: EFFORT]@row = "Low"), 4, IF(AND([OBJ: IMPACT]@row = "Low", [OBJ: EFFORT]@row = "Low"), 5)))))))))

see post for more details: https://community.smartsheet.com/discussion/74405/convert-high-medium-low-to-numbers-without-nested-if-statements#latest

Mark Cronk

Excellent. Thank you for using the Community and bringing ideas about new capabilities.

Mark

Mike.A

Sorry there was a double posting on this. My first didn’t seem to show up so I ended up posting again! Oh well... :-)