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

  • SmSulli
    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

     

     

  • Kal-El
    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

  • Kal-El
    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

  • SmSulli
    Options

    Maybe you could attach an image of your Smartsheet.

    Shawn

     

  • Kal-El
    Options

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

     

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

  • SmSulli
    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

     

     

    Capture1.JPG

  • Kal-El
    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

  • SmSulli
    Options

    angry

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

    Sorry

     

  • Deepa Manik
    Options

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

  • MAPerez
    MAPerez ✭✭
    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?

     

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

This discussion has been closed.