Parent-Child Formula for Percent Comnplete

Good Day All:

How can I change the following formula below into a parent child relationship so if I add additional rows, the parent will automatically include the child in the calculations for overall percent complete.

Does anyone have the Smartsheet % Complete Formula that is hard coded within the Project Settings?

Thanks for all your help in advance!

=([% Complete (Planned)]12 * [Duration (days)]12 + [% Complete (Planned)]18 * [Duration (days)]18 + [% Complete (Planned)]26 * [Duration (days)]26 + [% Complete (Planned)]27 * [Duration (days)]27 + [% Complete (Planned)]35 * [Duration (days)]35) / ([Duration (days)]12 + [Duration (days)]18 + [Duration (days)]26 + [Duration (days)]27 + [Duration (days)]35)

Find more posts tagged with

Formulas

Comments

SmSulli

Kal-El

You may be making this hard on yourself. I think you could use a simple formula in the parent row, % Complete column, =avg(children())

This is calculate a % complete using the children rows below.

Give that a try

Shawn

Kal-El

I tried the AVG and the calculations are not the same as the project settings for %complete. Any other ideas?

It would be great if Smartsheet provided the formula they have hard coded in project settings for calculating % Complete

Kal-El

I tried the AVG and the calculations are not the same as the project settings for %complete. Any other ideas?

It would be great if Smartsheet provided the formula they have hard coded in project settings for calculating % Complete

SmSulli

Maybe you could attach an image of your Smartsheet.

Shawn

Kal-El

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

Use the AVG Formula in the Summary Task (Execution). It Plan will not equal Actual

SmSulli

That was helpful! Here goes.

I'd suggest adding 2 extra columns for the calculations. This will allow you to add child rows and still calculate the Planned % automatically. These columns can be hidden.

Calc: =[Duration (days)]2 * [% Complete (Planned)]2

Calc2: =[Duration (days)]2

Then the parent row for each of these columns will be: =Sum(Children())

The parent row for % Complete (Planned): =Calc1 / [Calc2]1

This will give you the same percentage as the % Complete (Actual) parent row.

My formula for each % Complete (Planned) rows: =IF(AND(TODAY() >= [Planned Start Date]2, TODAY() <= [Planned Finish Date]2), (NETWORKDAYS([Planned Start Date]2, TODAY()) / [Duration (days)]2), IF(TODAY() > [Planned Finish Date]2, 1, 0))

Give it a shot!

Shawn

Kal-El

It is a good formula, except when you include task that have the same predecessors. The calc field will count the number whereas the Smartsheet formula only count it once.

For example, Task 1 and 2 have the same predecessor and finish on the same day. The total duration is 1 day instead of 2 days

SmSulli

So close, maybe a small manual adjustment to the calc column for the Start to Start predecessors, as needed.

Sorry

Deepa Manik

Can someone please provide the exact formula that is hardcoded in %complete column.

MAPerez

I think this is similar to a problem that I am having.

I have a column that indicates the percentage of the task that has been completed based on the highest valued checkbox that is checked.

I need to have the parent cell calculate the percentage completed based on the children, yet any formula that I've tried has not worked.

Any suggestions?

Screen Shot 2018-04-24 at 7.47.03 PM.png