"Projected % complete" & weighted average for parent rows
Hello - can anyone help me find a formula for ensuring that the 'expected % complete' of the Parent row is equal to the correct weighted average of the children rows?
I noticed that when I have multiple tasks, with a gap in timing, that the parent row takes the earliest start and the latest end date (which it should), but that my 'expected % complete' formula doesn't account for a gap between timing of deliverables (i.e. the gap in timing between rows 199 and 200 in the example of my plan below) so the parent row is delayed, even though none of the children rows are delayed.
FYIs in case you need them:
- I tried using AVGW formula in the 'expected % complete' column, but that didn't seem to work
- If it matters, I have 6 hierarchy levels and would like parent rows to take into account all of its child rows (like the automatically calculated % complete column)
- Below are the formulas I am currently using: (status formula works fine, but expected % complete only works for children rows))
- Status (basically, if a task is >90% on time, it's on track… if its 70-90% on time, its "at risk" and if its below 70% or past due, its delayed:
- IF([NDA Deliverable]@row = "", "", IF([% Complete (Actual)]@row = 1, "Complete", IF(AND([% Complete (Expected)]@row > 0, [% Complete (Actual)]@row < [% Complete (Expected)]@row * 0.71), "Delayed", IF(AND([% Complete (Actual)]@row > [% Complete (Expected)]@row * 0.7, [% Complete (Actual)]@row < [% Complete (Expected)]@row * 0.9), "At Risk", IF(AND([% Complete (Actual)]@row = 0, [% Complete (Expected)]@row = 0), "On Track (Not Started)", IF(AND([% Complete (Actual)]@row > 0, [% Complete (Actual)]@row >= [% Complete (Expected)]@row * 0.9), "On Track (In Progress)"))))))
- Expected % complete:
- MAX(MIN(IF([Duration (Work Days)]@row>0, ((TODAY()-[Start Date]@row) / ([End Date]@row -[Start Date]@row)), IF([Duration (Work Days)]@row = 0)), 1), 0)
- Status (basically, if a task is >90% on time, it's on track… if its 70-90% on time, its "at risk" and if its below 70% or past due, its delayed:
Any thoughts/ help someone can give is tremendously appreciated!
Thanks!
Brian
Best Answer
-
Put this in your actual on your parent rows to see if that help out. It will only average the % that is NOT "On Track (Not Started)"
=(Sumif(Children([Calculated Status(Automatic)]@row),"Complete", CHILDREN([% Complete (Actual)]@row))+Sumif(Children([Calculated Status(Automatic)]@row),"Delayed", CHILDREN([% Complete (Actual)]@row))+Sumif(Children([Calculated Status(Automatic)]@row),"At Risk", CHILDREN([% Complete (Actual)]@row))+Sumif(Children([Calculated Status(Automatic)]@row),"On Track (In Progress)", CHILDREN([% Complete (Actual)]@row)))/(Countif(Children([Calculated Status(Automatic)]@row),"Complete")+Countif(Children([Calculated Status(Automatic)]@row),"Delayed")+Countif(Children([Calculated Status(Automatic)]@row),"On Track (In Progress)")+Countif(Children([Calculated Status(Automatic)]@row),"At Risk"))
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
Answers
-
Good Morning @blawrence13
What if you tried an if function. In a helper column.
=IF(COUNT(ANCESTORS())=1,IF((COUNTIF(CHILDREN([Calculated Status(Automatic)]@row),"On track (Not started)"+COUNTIF(Children([Calculated Status(Automatic)]@row),"Complete")))/Count(CHILDREN([Calculated Status(Automatic)]@row))=1,"On track (Not started)"))
Then on the parent rows just change your formula to reference the helper row. To keep it a column formula just add this to the front.
=IF(COUNT(ANCESTORS())=1,[Helper Column]@row,IF([NDA Deliverable]@row = "", "", IF([% Complete (Actual)]@row = 1, "Complete", IF(AND([% Complete (Expected)]@row > 0, [% Complete (Actual)]@row < [% Complete (Expected)]@row * 0.71), "Delayed", IF(AND([% Complete (Actual)]@row > [% Complete (Expected)]@row * 0.7, [% Complete (Actual)]@row < [% Complete (Expected)]@row * 0.9), "At Risk", IF(AND([% Complete (Actual)]@row = 0, [% Complete (Expected)]@row = 0), "On Track (Not Started)", IF(AND([% Complete (Actual)]@row > 0, [% Complete (Actual)]@row >= [% Complete (Expected)]@row * 0.9), "On Track (In Progress)")))))))
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
I just saw the statement of the amount of hierarchy levels. I would have to rework the formulas how ever. Perhaps it will help you along the way until I get them rebuilt for you.
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
Put this in your actual on your parent rows to see if that help out. It will only average the % that is NOT "On Track (Not Started)"
=(Sumif(Children([Calculated Status(Automatic)]@row),"Complete", CHILDREN([% Complete (Actual)]@row))+Sumif(Children([Calculated Status(Automatic)]@row),"Delayed", CHILDREN([% Complete (Actual)]@row))+Sumif(Children([Calculated Status(Automatic)]@row),"At Risk", CHILDREN([% Complete (Actual)]@row))+Sumif(Children([Calculated Status(Automatic)]@row),"On Track (In Progress)", CHILDREN([% Complete (Actual)]@row)))/(Countif(Children([Calculated Status(Automatic)]@row),"Complete")+Countif(Children([Calculated Status(Automatic)]@row),"Delayed")+Countif(Children([Calculated Status(Automatic)]@row),"On Track (In Progress)")+Countif(Children([Calculated Status(Automatic)]@row),"At Risk"))
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
Thanks, Mark! This is super helpful! I really appreciate your time and help!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!