Need Help with Nested IF Function in Smartsheet

stevediaz

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!

Itai Perez

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.