"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 7090% 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)
Any thoughts/ help someone can give is tremendously appreciated!
Thanks!
Brian
Best Answer

I've only read the first few sentences.
 Create a days complete (expected) column
 Use this formula in the 'days completed {expected)' column =[duration (work days)]@row*[% complete (expected)]@row on all child row
 In the parent row of the % complete (expected) column use =SUM(CHILDREN([days completed {expected)]))/SUM(CHILDREN([duration (work days)]@row))
Answers

I've only read the first few sentences.
 Create a days complete (expected) column
 Use this formula in the 'days completed {expected)' column =[duration (work days)]@row*[% complete (expected)]@row on all child row
 In the parent row of the % complete (expected) column use =SUM(CHILDREN([days completed {expected)]))/SUM(CHILDREN([duration (work days)]@row))

This is tremendously helpful! THANK YOU!!!
BDL
Help Article Resources
Categories
Check out the Formula Handbook template!