How to disable duration dependencies without impacting aggregation of percentage complete

Gabe · 07/28/21

Hi,

I am hoping someone can help me with a smartsheet issue l am facing. l have created a project template because l like that any percentages you enter in the "% Complete" column will roll up or aggregate from children to parent rows (See attached screenshot).

l have noticed that the start/finish columns have an impact on the aggregation of percentages for "% Complete" column. For example, if l change the finish date for Project A to 08/20/2021, it changes the Initiatives % complete to 78% instead of 80%. I just want a simple aggregation for only % Complete column without having any dependency on other columns.

I tried disabling the dependency via project settings but that resulted in my percentages "not" aggregating from children to parent. Is there anyway to disable the impact that the duration has on the aggregation of numbers in the "%Complete" column? Please advise, thank you.

Jaykel Torres · 07/29/21

Hey @Gabe,

When Dependencies are enabled and a % Complete Column has been assigned within Project Settings, the Column will auto-calculate Parent Rows by a weighted percentage based on both the Duration and % Complete entered on each child row. There currently is not a way to prevent this from happening but you can disable the setting then create your own Formula within the Parent Rows to calculate the percentage:

If you simply want to just get the average of the Children Rows within your % Complete Column, I recommend using the AVG and CHILDREN Functions. I've created an example below, to demonstrate this:

Formula (in yellow): =AVG(CHILDREN())

This formula will calculate the average values of its Children Rows (B, C, and D).

I hope this helps!

Jaykel

Gabe · 07/29/21

@Jaykel Torres

Thank you Jaykel. I tried using the AVG functions but the hierarchy for my smartsheet has so many layers.

In the attached screenshot, each % complete entered in the project rolls up to the mandate, each mandate rolls up to the objective, each objective rolls up to the initiatives and finally each initiative rolls up to the portfolio.

We have about 17 initiatives with their own respective children items, close to 1200 rows in total. Do l have to apply the AVG(Children()) function at every single parent and manually select the children (initiatives, objective and mandate) OR is there an easier way? I did try applying the function at the portfolio level and selecting only the initiatives for its children but that returns a divided by zero error. Can you help to elaborate how l can use the AVG function to aggregate the data based on the second paragraphs logic.

Please advise and thank you.

Brett Wyrick · 08/03/21

https://community.smartsheet.com/discussion/comment/299550#Comment_299550

Hey Gabe.

Here's a solution I came up with that will auto-calculate the Total % Completed without a dependency or date columns.

Here's a link to a sheet with this data in it. You should be able to see the formulas here:

https://app.smartsheet.com/b/publish?EQBCT=245b0fb3f54249519de50d273ce62336

There's two percentage columns in this scenario (you can do it with one, but it's not as clean):

Total % Complete
% (in beige)

You only edit the % Complete column, and only for Child rows. I'd highly recommend adding some conditional formatting, as I'll review below. But first...

Here's the formula for the "Total % Complete" column:

=IFERROR(IF(COUNT(CHILDREN([%]@row)) > 0, AVG(CHILDREN([%]@row)), IF(AVG(CHILDREN()) > 0, AVG(CHILDREN()), "")), "")
Please note that this column should be a Column Formula.

That's it! If you'd like to add optional formatting, see below.

Let me know how this works for your situation!

-------------

The rest here is optional, but highly recommended.

Seeing as you have so many Parent columns, I'd highly recommend setting up an additional Column named "Hierarchy" with the following formula in it:

=IF(COUNT(CHILDREN([Name]@row)) = 0, "Child", IF(COUNT(ANCESTORS()) < 1, "Top Group", IF(COUNT(ANCESTORS()) < 2, "1st sub Group", IF(COUNT(ANCESTORS()) < 3, "2nd sub group", IF(COUNT(ANCESTORS()) < 4, "3rd sub group", IF(COUNT(ANCESTORS()) < 5, "4th sub group", IF(COUNT(ANCESTORS()) < 6, "5th sub group", IF(COUNT(ANCESTORS()) < 7, "6th sub group", IF(COUNT(ANCESTORS()) < 8, "7th sub group", IF(COUNT(ANCESTORS()) < 9, "8th sub group", IF(COUNT(ANCESTORS()) < 10, "9th sub group", "10th sub group")))))))))))

What this allows you to do is add conditional formatting for each row based upon its hierarchy in your sheet. Set it as a column formula for the "Hierarchy" column.

Then set up some conditional formatting based upon that Hierarchy column (i.e. "If Hierarchy column contains 'Group', make text bold", etc). Makes things much easier to view, and prevents users from adding data into the "%" if you set it up correctly:

Which makes the sheet look like this:

Note the red "15%" toward the bottom. That's a signal to the user that they added a percentage to a cell they shouldn't have (it's a Parent row). Leaving that incorrect percentage in there does affect the Parent rows above it, so it's important these are cleared.

Please keep in mind that this is not a "weighted" percentage complete like the native one in Smartsheet that takes Duration into consideration. This is simply an average. But if you're not working with dates, this works really well.

Let me know what you think!

How to disable duration dependencies without impacting aggregation of percentage complete

Answers

Categories