Add sub-tasks and weighted average

Marie_T

I created the same Baseline Start and Finish and Actual Start and Finish formulas as Paul Newcome. It worked really well.

Is there a way to add sub-tasks and incorporate the weighted average into the [Projected Percentage Complete] and [Actual Percentage Complete] columns?

Hope to hear from you soon. Thank you in advance for your assistance!

Genevieve P.

Hi @Marie_T

Other members in the Community may have a more succinct way of doing this, but I would use a Duration column to then find out the current number of days that have been completed based on the %, then use this to find the weighted %.

To set up the Parent Rows, you can bring in the MIN Start Date (earliest start date) of the Child rows, and the MAX End Date (latest end date) of the Child rows to calculate the total Duration for that Parent.

Start Date Parent Formula

=MIN(CHILDREN())

End Date Parent Formula

=MAX(CHILDREN())

Then my Duration can be based off of these two date columns, using NETWORKDAYS to find the number of working days:

=NETWORKDAYS([Baseline Start]@row, [Baseline End]@row)

You can set this as a Column Formula.

Once you have the duration for each Child Task, I would add in a Helper Column to Multiply this duration by the % Complete for that row, so in my sheet:

=[Baseline Duration]@row * [Projected % Complete]@row

This is so that you can see how many days the % complete represents. Then take this current Duration for the child rows and divide it by the Total Duration in the Baseline Duration column to find the total % of what is currently completed, weighted by the days of the child tasks:

=SUM(CHILDREN()) / SUM(CHILDREN([Baseline Duration]@row))

Then in the Projected % Complete top row you would want to use =Calculation@row to bring that Percent into the Parent Row.

Like I said, someone else may have a much better way of doing this; I'm not personally that familiar with trying to weigh tasks based on duration separate from simply using the Project Settings in the sheet. I will note that Baseline is a feature that Smartsheet is looking to implement in 2021 (see here) so hopefully that release will resolve some of this custom-creation needs!

Cheers,

Genevieve

Marie_T

Hi Genevieve,

Thank you for your answer. As you advised, I tried to use:

Start Date Parent Formula

=MIN(CHILDREN())

End Date Parent Formula

=MAX(CHILDREN())

Duration Formula

=NETWORKDAYS([Baseline Start]@row, [Baseline End]@row)

Unfortunately, it did not work. What do you set the column types for Start Date, End Date, and Duration columns?

Thank you very much for your help. I really appreciate it.

Marie

Genevieve P.

Hi @Marie_T

Would you be able to clarify what you mean when you say that "it did not work"? Are you receiving an error message or was incorrect data returned?

The Start Date and End Date columns are both Date Type of columns, and the Duration column is a Text/Number type of column.

Marie_T

Hi @Genevieve P

Thank you for getting back to me.

I was able to make it work now. Thank you.

I am completely new to Smartsheet – a beginner. By using your method, is it possible to create a line chart for “Projected % Complete” vs. “Actual Percentage Complete” that shows/includes the parents and sub-tasks?

Genevieve P.

Hi @Marie_T

My apologies for the delay!

The way I'd create a line chart from this data would be to first create a Report to pull in just the rows that are applicable. Then you can only show the three columns needed (Week Number, Actual Percent, and Projected Percent) and use this for your Chart Widget.

Were you able to figure this out? Is it working for you?

Cheers!

Genevieve

Marie_T

Hi @Genevieve P

Thank you for getting back to me. It is working for me.

Thank you.

Happy New Year!