Another nested IF syntax question
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?
Answers

@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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

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!

Thanks heaps all.
Not just an answer to my problem but value add as well.
👍️

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?

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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

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.

That's great, glad it's working now!
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
Check out the Formula Handbook template!