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
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)
Comments
-
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
-
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
-
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
-
Maybe you could attach an image of your Smartsheet.
Shawn
-
https://app.smartsheet.com/b/publish?EQBCT=ab18c1eb8260493c980b787589142463
Use the AVG Formula in the Summary Task (Execution). It Plan will not equal Actual
-
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
-
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
-
So close, maybe a small manual adjustment to the calc column for the Start to Start predecessors, as needed.
Sorry
-
Can someone please provide the exact formula that is hardcoded in %complete column.
-
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?
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 443 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 35 Webinars
- 7.3K Forum Archives