Calculate Expected completion %
I have added a column to my project plan template to calculate expected completion %. I want to the parent level rows to calculate an average based on the children. I'm thinking I need to add an IFERROR...
=IF(COUNT(CHILDREN()) > 0, AVG(CHILDREN()), IF(Start@row < Today@row, MIN((Today@row  Start@row) / (Finish@row  Start@row), 1), 0))
Best Answer

If your finish date is before today, it will give you a % that's over 100, right? In which case, we want to add a parameter that says if it was supposed to finish before today, then the Expected % complete would be 100% (not more)
This should work for you:
=IFERROR(AVG(CHILDREN()), IF(Finish@row <= TODAY(), 1, IF(Start@row <= TODAY(), NETWORKDAYS(Start@row, TODAY()), 0) / NETWORKDAYS(Start@row, Finish@row)))
Answers

A little bit depends on what you want it to do if there's an error.... If there are no children, would you want it to calculate the expected % complete for that task?
=IFERROR(AVG(CHILDREN()), IF(Start@row <= TODAY(), NETWORKDAYS(Start@row, TODAY()), 0) / NETWORKDAYS(Start@row, Finish@row))
The advantage of this last formula is that it works for every row  header or not... it will allow you to use it as a column formula. For rows that have no children, it will calculate the expected % complete based on the dates in that row

@MCorbin This works nicely, however where could I add in the MIN to keep the % from going over 100%?

With this formula, the % should never go over 100%.... Are you seeing that happen? If so, could you add a screenshot so we could see the dates?

If your finish date is before today, it will give you a % that's over 100, right? In which case, we want to add a parameter that says if it was supposed to finish before today, then the Expected % complete would be 100% (not more)
This should work for you:
=IFERROR(AVG(CHILDREN()), IF(Finish@row <= TODAY(), 1, IF(Start@row <= TODAY(), NETWORKDAYS(Start@row, TODAY()), 0) / NETWORKDAYS(Start@row, Finish@row)))

@MCorbin That worked, thank you!
Help Article Resources
Categories
Check out the Formula Handbook template!