Need Help with Nested IF Function in Smartsheet
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
-
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
If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂
https://www.linkedin.com/in/itai-perez/
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!