Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

%Completion Rollup of projects doesn't make sense for parents of parents

Hello, I'm realizing for a project, the % of completion of the Parent of a Parent task, doesn't consider the size of the Childs of the Parent task.

For example, in this case I have two Parent tasks, both of them are 10 days of work, one is 10 task of one day each, and the other is one task of 10 days. Once any of them is completed at 100%, half of the project should be considered completed, meaning 50%.

As you can see, when completing all tasks for Parent1, the overall completion jumps to 9% while the Parent2 moves it to 91%, meaning it's considering only the total duration of each parent and not what lies within it.

This makes even less sense when you create a single dependency, like in the following case where I changed nothing but duplicated the length of Parent 1, by adding a single dependency, in that case, any level of completion of Parent1 affects double to the overall completion.

The rollup formula should change to consider the weighted value of the parent task based on the amount of dates from its child tasks, to reflect the actual advance of a project's Gantt chart

Answers

  • Community Champion

    I had some fun with this one. The blue system column is calculating completion based off of immediate children, my custom yellow is looking at the individual descendants.

    Duration SUB (turn the durations into numeric values) =VALUE(SUBSTITUTE(Duration@row, "d", ""))
    Children (Helper column to determine which rows are lowest level tasks) =COUNT(CHILDREN())
    Weighted Completion (determine at the child level, how many days of the task have been completed) =IF(Children@row = 0, [Duration SUB]@row * [%Completion]@row, "")
    Descendants Durations Count (sum total number of descendants durations) =IF(Children@row = 0, "", SUM(COLLECT(DESCENDANTS([Duration SUB]@row), DESCENDANTS(Children@row), 0)))
    Individual Task Weights (compare the descendants weighted completion against the descendants total duration) =IFERROR(SUM(DESCENDANTS([Weighted Completion]@row)) / [Descendants Durations Count]@row, "")

    Jason Tarpinian - Sevan Technology

    Smartsheet Aligned Partner

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions