Calculate Expected completion %

Melissa Torrez

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))

MCorbin

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

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

Melissa Torrez

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

MCorbin

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?

Melissa Torrez

@MCorbin

MCorbin

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)))

Melissa Torrez

@MCorbin That worked, thank you!