Planned vs actual progress (Summary level)

Hi everyone, I am trying to calculate the % Planed based on the start and finished dates but when I try to set the Complete (actual) progress just to verify that all of my plan y on date, I see that the summary tasks are not calculated correctly. I am using the following formula

=ROUND(IF(TODAY() < [Baseline Start]@row , 0, IF(TODAY() >= [Baseline Finish]@row , 1, NETWORKDAYS([Baseline Start]@row , TODAY()) / NETWORKDAYS([Baseline Start]@row , [Baseline Finish]@row ))), 2)

As u see in the attachment, the %Planed is less than the %Complete but all of the following activities are according to the %Planed

I hope you can help me

thanks

Tania

Find more posts tagged with

Sheets

Formulas

Accepted answers

Cassie L

@ttabares to match what exists in the % Complete column, use the following formula under Column "%Planeado", row 1:

=AVGW(CHILDREN([%Planeado]@row ), CHILDREN(Duration@row ))

Andrée Starå

https://community.smartsheet.com/discussion/comment/494064#Comment_494064

Hi,

I hope you're well and safe!

I usually add a so-called helper column called Level with the formula below, and then you can use it to identify parents and use it for conditional formatting and more.

=IF(COUNT(CHILDREN([Task Name]@row )) > 0, COUNT(ANCESTORS()) + 1)

Would that work/help?

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Smartsheet Expert Consultant & Partner / CEO @ WORK BOLD

✅ Did I help answer your question/solve the problem? Please support with 💡 ⬆️ ❤️, and/or ✅ Answer. This will make it easier for others to find a solution or help answer. I appreciate it, thank you! 🙏

All comments

Cassie L

@ttabares Smartsheet uses a weighted average formula for % Complete based on the duration. Using the following formula in your highlighted cell, you can see how Smartsheet got this result:

=AVGW([% Complete]2:[% Complete]5, Duration2:Duration5)

ttabares

Hi Cassie! Thanks for your response. I am looking at my % Complete column and I don´t see any formula there. The values are placed manually. Do you mean that is better to use this formula to calculate the %Complete instead of doing it manually?

Regards

Cassie L

@ttabares The reason your parent level (Row #1) % Complete column is displaying a different % than your "%Planeado" column where you have actually calculated the average is that Smartsheet does not use a simple average for the % Complete rollup (the top row under the % Complete column that is not editable due to having children rows). Even though you will not see the formula listed in the column, you can replicate Smartsheet's calculation using the AVGW formula I posted above :)

ttabares

Hi Cassie, do you recommend me another way to track the progress in order to avoid the problem I am facing? I mean what other formula to calculate %Planed can I use to match to the %Complete

Regards

ttabares

@Cassie L

Is there any way to automatically identify parent and child tasks in a column without having to assign it to each parent task using the formula? In my example I have some other parent tasks inside the row 1

Regards

Andrée Starå

https://community.smartsheet.com/discussion/comment/494064#Comment_494064

ttabares

Hi Andrée thank you very much! the formula worked :)

Have a fantastic day!

Regards

Tania

Andrée Starå

Excellent!

Happy to help!

✅ Remember! Did I help answer your questions or solve the problem? Please support with 💡 ⬆️ ❤️, and/or ✅ Answer. It will make it easier for others to find a solution or help answer. I appreciate it, thank you! 🙏

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.