How Do I make a Gantt Chart automatically show 100% for Complete when Status is marked as "Complete"

tommyW89

I would like to automatically update the %Complete in the Gantt chart to 100% if the Status is set to "Complete" in the dropdown list. I can't seem to figure this out. I have tried entering calculations / IF("Complete", 100%, if(Not Started, 0%)) etc. but because there's parents calculations by default, these formulas cant be entered. I can manually update the % Complete for Children cells but this is cumbersome and can be prone to error.

Has anyone done this?

Tom

KPH

Hi @tommyW89

You have the correct syntax for IF :

=IF(logical_expression,value_if_true,[value_if_false])

and nested IF where you replace the value if false with another IF:

=IF(logical_expression,value_if_true,IF(logical_expression,value_if_true,[value_if_false]))

I did notice two things:

You need to include the cell reference in your logical expression. So to evaluate the "Status" column you would use:

=IF(Status@row="Complete","100%") etc. I'm not sure if you are doing this.

You may also want to make your new column a % format column and can then use "1" instead of "100%".

The next step is to include a logical expression that first checks if the row is a parent before looking at the status column. You can do this by counting the number of child rows. If the row has more than 0 children, the formula can return nothing. If the row has 0 children, the formula to fill the various percentages can be used. Here is an example of how that IF would look:

=IF(COUNT(CHILDREN(Task@row)) > 0, "this is a parent", "this is not a parent")

Then you can replace the text strings with formula.

Something like this:

=IF(COUNT(CHILDREN(Task@row)) > 0, "this is a parent", IF(Status@row = "Complete", 1, IF(Status@row = "Not started", 0)))

Then you can remove that placeholder text or replace it with the formula that you mention is used in the parent rows.

=IF(COUNT(CHILDREN(Task@row)) > 0, "", IF(Status@row = "Complete", 1, IF(Status@row = "Not started", 0)))

And make this a column formula. No need to edit anything in each row.