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

Find more posts tagged with

Accepted answers

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

All comments

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

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.