IF Formula % Complete to Status
Hello  I need some help. I think I am close but my formula isn't working. This is what I'm trying to accomplish:

% Complete => Project Status
Blank = Not started
less than 100% = In progress
100% = Complete

=IF(ISBLANK([% Complete]@row), "Not Started", IF([% Complete]@row) < 1, "In Progress", IF([% Complete]@row = 1, "Complete")))
Thank you!
Best Answer

You just have an extra closing parenthesis tucked in (second IF). Try this...
=IF(ISBLANK([% Complete]@row), "Not Started", IF([% Complete]@row < 1, "In Progress", IF([% Complete]@row = 1, "Complete")))
Answers

You just have an extra closing parenthesis tucked in (second IF). Try this...
=IF(ISBLANK([% Complete]@row), "Not Started", IF([% Complete]@row < 1, "In Progress", IF([% Complete]@row = 1, "Complete")))

Thanks Paul! I knew it was something minor... I really appreciate it!
a

Happy to help. 👍️

Hi, @Paul Newcome this conversation is helpful for me today. However, I'm getting a CIRCULAR REFERENCE error when I input this formula into the %complete column. What am I doing wrong? Also, how does this formula know that I am wanting the STATUS column to be updated? I thought the formula would have to include the STATUS column reference somewhere????
=IF(ISBLANK([% Complete]@row), "Not Started", IF([% Complete]@row < 1, "In Progress", IF([% Complete]@row = 1, "Complete")))
thanks for your help!
Lynda

@Lynda Ross The formula would go in the Status Column.

Thanks for the tips.
May I check with you if I have an inactive task (cancelled) , how can I prevent this children task from contributing the parent % complete?
My "Status" column is based on "% Complete" column.

@Angeline Yong What is your current formula that you are using?

Below is my formula in the status column
=IF([% Complete]@row = 0, "Not Started", IF([% Complete]@row = 1, "Complete", "In Progress"))
I am managing project which I created a gantt chart template. Each project will have slight different on the task.
If certain task is not applicable to that project, any way to exclude the % Complete of that irrelevant task to the Parent % Complete ?
Sorry for the late reply, did not get notification.

@Angeline Yong It is possible with a formula, but it would require either using a different column for % Complete that is not tied to the Gantt chart or removing dependencies. Either way it won't be tied to the project settings/gantt.

I see.
Thanks for the feedback @Paul Newcome
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 348 Global Discussions
 199 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 455 Show & Tell
 28 Member Spotlight
 1 SmartStories
 282 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!