Another nested IF syntax question

RobH

Hi all, I am trying to return a RYG symbol based on the conditions in two other columns each with a Low Medium High (LMH) option.

This is my formula...

=IF(Impact@row + [Probability/Urgency]@row = "LL", "Green", IF(Impact@row + [Probability/Urgency]@row = "LM", "Yellow", IF(Impact@row + [Probability/Urgency]@row = "ML", "Yellow", IF(Impact@row + [Probability/Urgency]@row = "MH", "Red", IF(Impact@row + [Probability/Urgency@row = "HM", "Red", IF(Impact@row + [Probability/Urgency]@row = "HH", "Red", ""))))))

I am very new to Smartsheet formulae. Can anyone see any obvious errors?

Jeff Reisman

@RobH Easy typo to make - you're missing the right bracket on [Probability/Urgency]@row here:

One thing I might do different would be to add a hidden helper column where you add the Impact and Probability/Urgency values together, and then reference the helper column in your formula instead. On a large sheet this can save resources and speed up sheet saves/refreshes, etc.

Nick Korna

Hi @RobH,

There is a missing square bracket in the "HM" part of the formula:

This is what is causing the error to appear.

Alternatively you can use IF & OR to make a formula that does something similar:

=IF(Impact@row + [Probability/Urgency]@row = "LL", "Green", IF(OR((Impact@row + [Probability/Urgency]@row = "ML"), (Impact@row + [Probability/Urgency]@row = "LM")), "Yellow", IF(OR((Impact@row + [Probability/Urgency]@row = "MH"), (Impact@row + [Probability/Urgency]@row = "HM"), (Impact@row + [Probability/Urgency]@row = "HH")), "Red", "")))

One other thing which is not an error, but a possible oversight is that LH/HL gives 0 result - i.e. the status will be blank:

Hope this helps, if you've any questions etc. then just ask!

RobH

Thanks heaps all.

Not just an answer to my problem but value add as well.

👍️

RobH

OK, so I have made the changes to the formula as Suggested and am still getting an #UNPARSEABLE error.

This is the revised formula with the additional conditions added as per Nick's comment...

=IF(Impact@row + [Probability/Urgency]@row = "LL", "Green", IF(Impact@row + [Probability/Urgency]@row = "LM", "Green", IF(Impact@row + [Probability/Urgency]@row = "ML", "Green", IF(Impact@row + [Probability/Urgency]@row = "MM", "Yellow", IF(Impact@row + [Probability/Urgency]@row = "HL", "Yellow", IF(Impact@row + [Probability/Urgency]@row = "LH", "Yellow" IF(Impact@row + [Probability/Urgency]@row = "MH", "Red", IF(Impact@row + [Probability/Urgency]@row = "HM"; "Red", IF(Impact@row + [Probability/Urgency]@row ="HH", "Red", "")))))))))

I have the column that is supposed to show the symbol formatted as "Symbol" and the columns that set the conditions (LMH) formatted as "Dropdown". Is this correct?

Jeff Reisman

@RobH

I would highly recommend moving your Impact@row + [Probability/Urgency]@row into a helper column. Once that's done, you can significantly reduce the size and complexity of your formula by grouping all the combinations for each color together using OR functions:

=IF(OR(Helper@row = "LL", Helper@row = "LM", Helper@row = "ML"), "Green", IF(OR(Helper@row = "MM", Helper@row = "HL", Helper@row = "LH"), "Yellow", IF(OR(Helper@row = "MH", Helper@row = "HM", Helper@row = "HH"), "Red", "")))

So much less complex, and way easier to troubleshoot.

RobH

Great Jeff, I created the helper column using a JOIN function and then applied your IF/OR function above and it works perfectly.

Thank you.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/362877#Comment_362877

That's great, glad it's working now!