Parent-Child Formula for Percent Comnplete

Kal-El

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)

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.

我建议为它添加两个额外的列tions. 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.

我的公式为每个%完成(计划)行:=(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?