Need Help with Nested IF Function in Smartsheet

Options

Hello Smartsheet community,

I'm currently working on a project and need some assistance with a complex formula involving nested IF functions in Smartsheet. Here's what I'm trying to achieve:

I have a sheet with a "Status" column that contains different values such as "In Progress," "On Hold," "Completed," and "Not Started." Additionally, there's a "Priority" column with values ranging from 1 to 5, where 5 indicates the highest priority.

I want to create a formula that calculates a "Score" based on the "Status" and "Priority" columns. Here's the logic I'd like to apply:

  • If the task is "Completed," the Score should be 100.
  • If the task is "In Progress" and has a Priority of 4 or 5, the Score should be 75.
  • If the task is "In Progress" and has a Priority of 1, 2, or 3, the Score should be 50.
  • If the task is "On Hold," the Score should be 25.
  • If the task is "Not Started," the Score should be 0.

I've attempted to create the formula but seem to be encountering issues with the nested IF functions.

Could someone kindly help me with the correct syntax and structure for this formula? Your guidance and expertise are much appreciated.

Thank you!

Answers

  • Itai
    Itai ✭✭✭✭✭✭
    Options

    Hi @stevediaz

    Here is a formula that should work:

    =IF(Status@row = "completed", 100, IF(AND(Status@row = "In Progress", OR(Priority@row = 4, Priority@row = 5)), 75, IF(Status@row = "On Hold", 25, IF(Status@row = "Not Started", 0, 50))))

    Let me know if I missed anything.

    Itai Perez

    Project Manager | Transformation Department

    Gong cha

    If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂

    https://www.linkedin.com/in/itai-perez-740543116/

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!