IF Formula % Complete to Status

Alicia Penner

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!

Paul Newcome

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")))

Paul Newcome

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")))

Alicia Penner

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

-a

Paul Newcome

Happy to help. 👍️

Lynda Ross

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

Paul Newcome

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

Angeline Yong

Thanks for the tips.

@Paul Newcome

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.

Paul Newcome

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

Angeline Yong

@Paul Newcome

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.

Paul Newcome

@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.

Angeline Yong

I see.

Thanks for the feedback @Paul Newcome