Another nested IF syntax question

Options
RobH
RobH ✭✭
edited 02/16/23 in Formulas and Functions

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

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    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

    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!

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    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!

  • RobH
    RobH ✭✭
    Options

    Thanks heaps all.

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

    👍️

  • RobH
    RobH ✭✭
    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?



  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    @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.

    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!

  • RobH
    RobH ✭✭
    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.

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!