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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.4K Get Help
 321 Global Discussions
 197 Industry Talk
 418 Announcements
 4.2K Ideas & Feature Requests
 127 Brandfolder
 154 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!