#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# Parent-Child Formula for Percent Comnplete

Options

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)

Tags:

## Comments

• Options

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

• Options

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

• Options

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

• Options

Maybe you could attach an image of your Smartsheet.

Shawn

• Options

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

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

• Options

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

• Options

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

• Options

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

Sorry

• Options

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

• ✭✭
edited 04/25/18
Options

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?

This discussion has been closed.