Dynamic Status
A more refined and slightly more universal solution to the first posting is as follows:
=IF([% Complete]18 = 0, "NOT STARTED", IF(AND([% Complete]18 > 0, [% Complete]18 < 1), " IN PROGRESS", IF([% Complete]18 = 1, "COMPLETE", IF([% Complete]18 > 1, "INVALID ENTRY", IF([% Complete]18 < 0, "INVALID ENTRY")))))
This assumes that "% Complete" is calculated from something like "=[Task Hours Completed]18/[Task Total Hours]18"
If you are entering the "% Complete" column manually, be sure you enter your value as a decimal value between 0 and 1 and make your column a % column by selecting the column and clicking "%" in the tool bar. The above formula will work, but the following formula is all you will need:
=IF([% Complete]18 = 0, "NOT STARTED", IF(AND([% Complete]18 > 0, [% Complete]18 < 1), " IN PROGRESS", IF([% Complete]18 = 1, "COMPLETE")))
This formula will enter:
"NOT STARTED" if % Complete = 0,
"IN PROGRESS" if 0 < % Complete < 1,
"COMPLETE" if % Complete = 1 and
"INVALID ENTRY" if % Complete < 0 or if % Complete > 1.
Note on conditional formatting:
If you adjust the % values such that you have at least one of the 4 different results showing in your column somewhere ("COMPLETE", "IN PROGRESS", "NOT STARTED", "INVALID ENTRY"), then you can add rules to conditionally format the "Status" column such that, if Status is "Complete" then make the background green and the font black. I added a screenshot of my rules.
I hope this helps someone, somewhere.
I am curious if anyone will use this. If you do use it, let me know by commenting, or by clicking the heart. This is purely for my own edification.
Comments

Hi Ben,
Nicely done!
Thanks for sharing!
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:[email protected]  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

A more refined solution is as follows:
=IF([% Complete]18 = 0, "NOT STARTED", IF(AND([% Complete]18 > 0, [% Complete]18 < 1), " IN PROGRESS", IF([% Complete]18 = 1, "COMPLETE", IF([% Complete]18 > 1, "INVALID ENTRY", IF([% Complete]18 < 0, "INVALID ENTRY")))))
This assumes that "% Complete" is calculated from something like "=[Task Hours Completed]18/[Task Total Hours]18"
The conditional formatting is still the same as I described above.
I'm curious if anyone will use this. If you do use it, let me know by responding here, or clicking the heart. Thanks.

To refine it even further you could use the @row function so you wouldn't need to think about row numbers.
=IF([% Complete]@row = 0, "NOT STARTED", IF(AND([% Complete]@row > 0, [% Complete]@row < 1), " IN PROGRESS", IF([% Complete]@row = 1, "COMPLETE", IF([% Complete]@row > 1, "INVALID ENTRY", IF([% Complete]@row < 0, "INVALID ENTRY")))))
Hope that helps!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:[email protected]  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
Check out the Formula Handbook template!