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

  • Jason Duryea
    Jason Duryea ✭✭✭✭✭✭

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!