Invalid Operation on nest if formula

I've used the nested if formula a number of times, but can I get it to work for this one? Nope!

I have a single select dropdown column with statements. In another column, I want the statement to return a different text value. The only time it works is when the first option (first IF statement) is selected. Could it be that the statements are too long?

=IF(Priority@row = "There is risk if we do not deliver or is a regulatory requirement", "Highest", =IF(Priority@row = "Will advance a specific strategic directive or addresses significant barriers to operations", "High", =IF(Priority@row = "Important to advancing our strategy or sets the foundation for future work", "Medium", =IF(Priority@row = "Addresses gaps or pain points that would add value but not urgent", "Low", =IF(Priority@row = "Work has potential but can wait to be actioned until other work is complete", "Lowest")))))



Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    Hi @JBYYC ,

    You don't need the = in front of the IF statements except for the first one. Your formula should be along the lines of:

    =IF(Priority@row = "There is risk if we do not deliver or is a regulatory requirement", "Highest", IF(Priority@row = "Will advance a specific strategic directive or addresses significant barriers to operations", "High", IF(Priority@row = "Important to advancing our strategy or sets the foundation for future work", "Medium", IF(Priority@row = "Addresses gaps or pain points that would add value but not urgent", "Low", IF(Priority@row = "Work has potential but can wait to be actioned until other work is complete", "Lowest")))))

    Hope this helps!

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    Hi @JBYYC ,

    You don't need the = in front of the IF statements except for the first one. Your formula should be along the lines of:

    =IF(Priority@row = "There is risk if we do not deliver or is a regulatory requirement", "Highest", IF(Priority@row = "Will advance a specific strategic directive or addresses significant barriers to operations", "High", IF(Priority@row = "Important to advancing our strategy or sets the foundation for future work", "Medium", IF(Priority@row = "Addresses gaps or pain points that would add value but not urgent", "Low", IF(Priority@row = "Work has potential but can wait to be actioned until other work is complete", "Lowest")))))

    Hope this helps!

  • JBYYC
    JBYYC ✭✭✭

    @Nick Korna you are officially my new favourite person. THANK YOU! *she says while slapping her forehead, ashamed of the silly error*

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!