Using AVGw function

Hi Everyone,

In my Smartsheet project plan I originally set up a Planned % column using the following formula to calculate progress based on planned dates:

=IF(  OR(ISBLANK([Planned Start]@row ), ISBLANK([Planned Finish]@row )),   "",   IF(PARENT(Primary@row ) = "External Dependencies",      "",      IF(       TODAY() > [Planned Finish]@row , 1,        IF(TODAY() < [Planned Start]@row , 0,   NETWORKDAY([Planned Start]@row , TODAY()) / NETWORKDAY([Planned Start]@row , [Planned Finish]@row )       )   )  ))

That worked fine, but I noticed the top-level parent rows weren’t reflecting reality. To address this, I created a new column and tried using a weighted average with this formula:

=IF(  COUNT(CHILDREN()) = 0,   [Planned %]@row ,   AVGW(COLLECT(CHILDREN([Planned %]@row ), CHILDREN([Planned %]@row ), @cell  <> ""),    CHILDREN(Duration@row )  ))

The issue is that one of the parent rows still isn’t showing the correct percentage. For example, it should calculate to 6%, but instead it displays 3%.

I’ve tried several variations but can’t seem to get it right. Has anyone run into this before, or know why the weighted average works for some parents but not others?

the blue shows the original column and the wrong 27 planned% total, and the test column is my pass at a new %planned column which does parents right. as you can see the original formula (which calculated the total % planned does it right for other parents at least to the level that I see) but it doesn't do that for the main top level row (what should be 6 is now 3 and I am not even sure the 7.18 is accurate or not)

Thanks in advance for any guidance!

@Paul Newcome @Ray Lindstrom please help

Find more posts tagged with

Formulas

Accepted answers

All comments

Cassie L

@mahmadi I believe the issue is that Smartsheet’s CHILDREN() only pulls direct children, not grandchildren. So if your top-level parent has children that are themselves parents, you're missing their data.

Potential Fix: Use a Helper Column to Roll Up Values

Create a helper column (e.g., Rollup %) in all rows.
In each leaf-level task (no children), calculate [Planned %].
In each parent row, use a formula like:

=IF(COUNT(CHILDREN())=0,[Planned %]@row ,AVGW(COLLECT(CHILDREN([Rollup %]@row ),CHILDREN(Duration@row ),> 0),CHILDREN(Duration@row )))

—> This way, each level rolls up from its direct children, and the top-level parent will reflect the full hierarchy if all intermediate levels are rolling up correctly.

Best,

Cassie

Source: Copilot

mahmadi

thank you. do you know the formula to apply to this helper column so it automatically reflects the result based on a task being a leaf or parent. the schedule is very long and if I have to do t manually for all leaf tasks and parents it will take a long time. should I simply do the if(or( ?

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.