# Another nested IF syntax question

Options
✭✭
edited 02/16/23

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?

• ✭✭✭✭✭✭
edited 02/16/23
Options

@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

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭✭
Options

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!

• ✭✭
Options

Thanks heaps all.

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

👍️

• ✭✭
Options

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?

• ✭✭✭✭✭✭
Options

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

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!