I need assistance with understanding how the % Complete for parent tasks in Smartsheet. Specifically, I’m trying to understand how Smartsheet computes these percentages at higher hierarchy levels when using formulas.
Even though the Actual%Complete and my calculate column of "Developed %" values for child tasks are similar, when rolling up to the 3 level numbers are varying quite a lot. is there something i am missing. Is there a solution that already exists for the calculating the target % percent for each task and their parents
As per my assumption, it is a calculate by a weighted average on the duration and % complete of immediate chlidren. In my attempt to manually calculate the target percent complete for a parent task based on its child tasks, there seems to be a discrepancy in the values at the parent level.
Details of my Setup:
1. Column "Completed Working days": Based on a certain date this give me the number of days invested for a task,
Formula: =IF(OR(ISBLANK([Planned Start Date]@row ), ISBLANK([Planned End Date]@row ), ISBLANK([Status Date]#)), 0, IF([Status Date]# < [Planned Start Date]@row , 0, IF([Status Date]# > [Planned End Date]@row , NETWORKDAYS([Planned Start Date]@row , [Planned End Date]@row ), NETWORKDAYS([Planned Start Date]@row , [Status Date]#))))
2. Column "Calculated Duration" : based on the start and end date of a task calculated the working days
Formula: =IF(ISBLANK([Planned Start Date]@row ), 0, NETWORKDAYS([Planned Start Date]@row , [Planned End Date]@row ))
3. Column "Target % at task" based on the completed working days and calucated duration, provides an expected completion of each task
Formula: =[Completed Working days]@row / [Calculated Duration]@row
4. Column "Parentname" defines the parent of each task
Formula: =PARENT([Task Name]@row )
5. Column "Weight" based on the duration calculated for each task, calculates the weight of each task under a parent
Formula: =[Calculated Duration]@row / SUMIFS([Calculated Duration]:[Calculated Duration], Parentname:Parentname, Parentname@row )
6. Column "Weight*target" multiplies weight with the Target calculated in 3rd point
Formula: =Weight@row * [Target % at task]@row
7. Column "Developed %" for parent tasks sums up the immidiate chidlren's Weight*Target
Formula: =IF(COUNT(CHILDREN([Task Name]@row )) = 0, [Target % at task]@row , SUM(CHILDREN([Weight*target]@row )))
Despite these calculations, the parent task in Smartsheet shows a different % Complete value. Could someone provide clarity on the methodology or formulas Smartsheet uses for these calculations? Understanding the exact algorithm or approach would greatly aid in aligning manual calculations with those generated by the platform.
