Formula to update a value
I would like my status field to automatically update based on the % complete. If 0% completed = "Not Started", If 100% = "Completed", and if anything over 0 but under 100 = "In Progress"
This is what I have so far but it is not picking up any of this formula except for =0 and so if the % is 100 this is returning In Progress. If 10%, it returns In Progress. If = 0, returns "Not Started"
=IF([% Complete]@row = 0, "Not Started", IF(AND([% Complete]@row > 0, [% Complete]@row < 100), "InProgress", IF([% Complete]@row = 100, "Complete", "Error")))
Answers
-
I have a similar formula in our project schedules. Instead of [% Complete]@row = 100, you have write it as [% Complete]@row = 1 because technically 100% is read as 1.0
=IF(ISBLANK([% Complete]@row), "", IF([% Complete]@row = 1, "Complete", IF([% Complete]@row > 0, "In Progress", IF([% Complete]@row = 0, "Not Started"))))
I think your formula is working, but the order of the logic might be causing the problem. As you can see in my formula, it checks for 100% first before checking for values greater than 0.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!