nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Smartsheet Community

Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.

Exclude canceled tasks from the automatic calculation of the parent's % complete

LaurieA

Hello everyone,

I'm working on a project sheet (with dependencies enabled and the Gantt view) and I'm looking for a way to exclude canceled tasks from the automatic calculation of the % complete on parent rows.

My requirement :
When a child task changes to “Canceled” status, I want it to no longer be included in the parent’s % complete. The planned duration of this canceled task represents buffer time that I want to keep visible—so I can’t set the duration to 0.

What I’ve Tried :
A. Formula with CHILDREN() in a custom column → #UNPARSEABLE. On a project sheet, CHILDREN() only works in Gantt system columns (% Complete, Duration, Dates).

B. SUMPRODUCT / COLLECT with CHILDREN() → Not supported with filter conditions on project sheets.

C. Column formula → Does not support CHILDREN() / PARENT().

What I don’t want to do :

Set the % Complete to 100% for canceled tasks (because this distorts actual progress tracking)
Set the duration to 0 (because this is buffer time that I want to keep)

My question :
Have you found a solution to recalculate a parent’s % complete while excluding certain child tasks on a project sheet with dependencies? Is there a workaround using auxiliary columns or another approach?

Thanks in advance for your ideas! 🙏

Find more posts tagged with

Sheets

Formulas

Automations

Project Management

Accepted answers

All comments

Matt_Burrows

Hey @LaurieA

Here's something you could try:

So, the % complete column is a system column, so we'll have to ignore that.

If you need to employ a column formula then I'd suggest two new columns - one for parent, and one for child.

Custom child:

=IF(COUNT(CHILDREN()) > 0, "", IF(Status@row = "Cancelled", "", [% Complete]@row))

Custom parent:

=IF(COUNT(CHILDREN([Task Name]@row)) = 0, "", SUM(CHILDREN([Custom child]@row)) / SUMIF(CHILDREN(Status@row), <>"Cancelled", CHILDREN(Duration@row)))

Hope this helps, let me know how it goes.

Matt

LaurieA

It works perfectly, thank you !

Paul Newcome

You don't need separate columns for parent and child formulas. You can combine them into a single column formula like so:

=IF(COUNT(CHILDREN([Task Name]@row)) = 0, IF(Status@row <> "Cancelled", [% Complete]@row), AVG(COLLECT(CHILDREN(), CHILDREN(Status@row), @cell <> "Cancelled")))

LaurieA

That's even better ! Thanks for the solution !

Paul Newcome

Happy to help. 👍️

Shaz

@LaurieA you will find strange behaviour of % completion column with average formula, you can test it against smartsheet % calculation vs Average formula.

To get the actual weighted average % completion that smartsheet's backend calculates with your cancel logic
1. Add a helper column to find the Earned Duration ( duration * % complete )
=IF(Status@row = "Cancelled", 0, Duration@row * [% Complete]@row)
2. Calculate weighted average for the % completion using sumprodcut logic
=IF(COUNT(CHILDREN([Task Name]@row)) > 0, SUM(CHILDREN([Earned Duration]@row)) / SUMIF(CHILDREN(Status@row), <>"Cancelled", CHILDREN(Duration@row)), IF(Status@row = "Cancelled", 0, [% Complete]@row))

LaurieA

Oops, there's a problem with this formula. I double-checked it, and unfortunately, the canceled line isn't excluded from the “Custom % Complete” calculation. So my average is skewed.

Here on my example I got 22% instead of 28%

I obtained : (12+32+14+56+0)/5 = 22%
but I want : (12+32+14+56)/4 = 28% → cancelled line (in gray) totally excluded from calculation, not count as 0.

LaurieA

If I have more than 2 activities, I finnaly got a wrong number in this case
(The gray line is the cancelled activity)

LaurieA

The problem that I don't want to put 0 in % Complete, because it's not a task that did not start but a task that finally doesn't exists. Well it exists in terms of duration but not in completion.
I would like the formula consider this line like non existant regarding % Complete and do not take it in account in calculation, but still consider it in terms of duration. Otherwise all is wrong.

Shaz

@LaurieA you dont need to update the % completion to 0. When you use children in the formula, it will take all rows into count even if its empty.

Paul Newcome

Can you provide a screenshot that also includes the statuses as well as the exact formula you are using?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.