Overall % Complete of all sub-deliverables with 'Development' in name
Hello,
I am attempting to create 2 separate sheet summary fields that specify an overall % complete based on sub-parent task names. I would like to create an overall % complete for all 'PIR Facility Components' tasks as well as a combined overall % complete for 'Facility Application Development Changes' and 'Host/AN Program Changes'; basically providing overall % complete of requirements gathering (PIR Facility Components) as well as software development tasks (Facility Application.... and Host/AN Program changes). I'm not sure if it's relevant, but I am using the =avg(children()) formula to acquire my percentages and not the % complete of child rows + assigned duration method of determining % complete.
I can add "Development" to the 'Host/AN Program Changes' sub-parent tasks so "Development" exists in both development-specific tasks if that makes the formula easier. Any ideas?
I appreciate any insight. Thank you!
Best Answers
-
You'll need a column that brings the parent data onto every row.
=PARENT([Task Name]@row)
From there you would use something like this to get the overall %:
=AVG(COLLECT([% Complete]:[% Complete], [Helper Column]:[Helper Column], @cell = "Facility Application Development Changes"))
-
You would need to incorporate an OR function:
=AVG(COLLECT([% Complete]:[% Complete], [Helper Column]:[Helper Column], OR(@cell = "Facility Application Development Changes", @cell = "Host/AN Program Changes")))
Answers
-
You'll need a column that brings the parent data onto every row.
=PARENT([Task Name]@row)
From there you would use something like this to get the overall %:
=AVG(COLLECT([% Complete]:[% Complete], [Helper Column]:[Helper Column], @cell = "Facility Application Development Changes"))
-
That worked, Paul. Thank you. Per my table above, could you please let me know how I would average both "Facility Application Development Changes" and "Host/AN Program Changes" deliverables?
I appreciate your time.
-
You would need to incorporate an OR function:
=AVG(COLLECT([% Complete]:[% Complete], [Helper Column]:[Helper Column], OR(@cell = "Facility Application Development Changes", @cell = "Host/AN Program Changes")))
-
That did it. Thanks again Paul. Appreciate your time.
-
Happy to help. 👍️
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 444 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