What formula to use in Status column based on % Complete column?
Hi, I'm stumped...I've tried this formula multiple different ways and nothing seems to work.
Status column: Not Started, In Progress, or Complete
% Complete column: any number (column is set to %'s)
This is what I need: IF % Complete = 0, Status=Not Started, IF % Complete is greater than 0 and less than 100, Status=In Progress, IF % Complete = 100, Status=Complete.
This is the last example of what I tried=IF([% Complete]@row = 0, "Not Started", IF(AND([% Complete]@row > 0, [% Complete]@row < 100), "In Progress", IF([% Complete]@row = 100, "Complete")))
Thanks in advance.
Best Answer

Hi @amber.lange & @John Hamilton
Just jumping in here to clarify that when you're looking at a % formatted type of column, the % is actually seen as a decimal in formulas. This means that instead of looking for 1 and 100, you'll want to look for 0.01 and 1.
Try John's same formula but with decimals instead:
=IF([% Complete]@row < 0.01, "Not Started", IF([% Complete]@row < 1, "In Progress", "Complete"))
You could also write this another way around:
=IF([% Complete]@row >= 1, "Complete", IF(OR([% Complete]@row = "", [% Complete]@row = 0), "Not Started", "In Progress"))
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Answers

See if this might work for you.
=IF([% Complete]@row < 1, "Not Started", IF([% Complete]@row < 100, "In Progress", "Complete"))

@John Hamilton Thank you, I believe I already tried that, but did test again. The issue keeps coming up that when the % Complete is 100%, the status column will not update to Complete. If I remove the formatting of percentages from the % Complete column, the formulas seem to work, but we would prefer to keep the format as a percentage. Make sense?

I set up some test data and it seemed to work for me using numbers only with no % sign.

Hi @amber.lange & @John Hamilton
Just jumping in here to clarify that when you're looking at a % formatted type of column, the % is actually seen as a decimal in formulas. This means that instead of looking for 1 and 100, you'll want to look for 0.01 and 1.
Try John's same formula but with decimals instead:
=IF([% Complete]@row < 0.01, "Not Started", IF([% Complete]@row < 1, "In Progress", "Complete"))
You could also write this another way around:
=IF([% Complete]@row >= 1, "Complete", IF(OR([% Complete]@row = "", [% Complete]@row = 0), "Not Started", "In Progress"))
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
@Genevieve P. Thanks for the clarification. Always great to learn new "stuff"

@Genevieve P. Thank you! That worked perfectly and the explanation was so helpful.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.3K Get Help
 321 Global Discussions
 197 Industry Talk
 418 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!