Need % Complete rollup to exclude tasks not needed in overall calculation of % done

amy pelayo

I have a master project plan where all tasks are listed.  I want to use this plan for any project and if a tasks is not needed I would like to keep the row but have a status of N/A. I would then like to have the % complete formula for the hierarchy to only calculate those rows that are needed so the overall % completion is not skewed with tasks that will never be used.  

I have tried many formulas and not sure which one would make sense - I also have not been able to accurately get any of the formulas I tried to create to even work.  IF(AND?  AVG(CHILDREN? SUMIF?  

On thing to note is that I took off the project dependencies because that was not leaving out those tasks that would not be used. Not sure if that can be used or not. 

 

Ezra

Look at using the weighted average function..

Paul Newcome

Unfortunately the weighted average (in my experience) is very cumbersome and not always accurate for this particular scenario.

 

I have found that 

 

=AVG(COLLECT(............................)) works very well for this.

Paul Newcome

Try something along the lines of

 

=AVG(COLLECT(CHILDREN(), CHILDREN([Status Column]@row), <> "N/A")

amy pelayo

Paul and Everyone, Thank you so much.  IT WORKED exactly how I needed it to!  Should have reached out to the community a few days ago.  All the best to you all, Amy

amy pelayo

Paul, Can I burden you one more time, I am getting a # Divide by zero for a parent with children that I have all set to N/A - is there a way to show this as blank or 0? - I attached a screenshot of my sheet and what it looks like .. Thanks, Amy

Genevieve P.

Hi Amy,

You may want to look into adding an IFERROR function at the beginning of your formula. For example:

 

=IFERROR((your formula), "0")

amy pelayo

Genevieve, thank you so much - this was very helpful.  Regards, Amy

Paul Newcome

Happy to help! 

amy pelayo

With all of your help I am making my way - wish I could just use the Project dependencies but I needed the rows I don't need to not be figured in on the % Complete.  I have two issues that remain, have added the IFERROR and it worked. 

1) I now need to know how to get the Date start / End date at the sub children to roll up along with duration

2) I also need the rollup of some sub sub children and the top parent - I need Date Start/Date End, overall %, and the duration rollup.  

Parent - Total of the sheet - Start Date / End Date  &  Overall % Complete   &   Duration Remaining

    Child Training - first level child  - Need % here

        Internal  % here

        External   % here

Regards, Amy

Paul Newcome

Are you able to provide a screenshot that shows the overall layout of the sheet to include the various columns mentioned along with the different hierarchy levels? 

 

Sensitive/confidential info can be blocked, removed, or replaced with "dummy data".

 

This would make it much easier to give you formulas that are more specific to you.

amy pelayo

Thank you Paul. I have attached two screenshots. The first is the overall top of the plan where I need the first level indents to roll up, the second screenshot is the 2nd level indent where I need them to rollup to the first level indent.  I am needing the dates to show up at each level and the duration, then I need the overall rollup and the 1st level indent where there are sub tasks as well. I have highlighted the rows in red.  Appreciate all your time on this.  Regards, Amy

Paul Newcome

Ok. This is actually pretty straight forward. The basis of our formulas will be 

 

COLLECT(CHILDREN(), CHILDREN(Status@row), <> "N/A")

.

This will collect all of the data for the children of whatever column the formula is in for each row within the children where the status is not N/A.

 

The End Date and Duration are the only two that should vary based on your use. I will explain that last, give options for each, and explain those differences.

.

% Complete: We want an average, so we wrap an AVG function around the above formula

 

=AVG(COLLECT(CHILDREN(), CHILDREN(Status@row), <> "N/A"))

.

Start Date: I am assuming you want the earliest Start Date, so we wrap a MIN around the above.

 

=MIN(COLLECT(CHILDREN(), CHILDREN(Status@row), <> "N/A"))

.

Duration: This is where another question would need answered.

How do you want to calculate this? Average? Total of Children? Start Date to End Date?

 

If it is the average you want, you would use the same formula as you did for the % Complete.

If you want the total, you would use a SUM function wrapped around the COLLECT instead of an AVG.

For Start Date to End Date, you would use

=[End Date]@row - [Start Date]@row

 

Since the dates are populated based off of the CHILDREN already, you will not need to worry about duplicating the COLLECT section if you are going with the third option.

.

End Date: Another question that will need answered...

Do you want to show the latest date populated, or do you want to leave this blank until all children are complete meaning the parent is complete?

Either way we are going to wrap a MAX function around the COLLECT portion.

 

=MAX(COLLECT(CHILDREN(), CHILDREN(Status@row), <> "N/A"))

 

If you are going with the first option, then that is done. If you were wanting to go with the second option, we would just use an IF statement to say that if the count of non-"N/A" children equals the count of children that are dates, use the MAX, otherwise leave blank.

 

=IF(COUNTIFS(CHILDREN(Status@row), <> "N/A") = COUNTIFS(CHILDREN(), ISDATE(@cell)), MAX(COLLECT(CHILDREN(), CHILDREN(Status@row), <> "N/A")))

.

Let me know if this helps.

amy pelayo

Paul - THANK YOU so much, sorry that I have not responded sooner as I have been on travel for 2 weeks.  I think I am about 95% done with my plan.  The only thing that I am having trouble with is the overall % complete. In the attached screenshot the only thing that I cannot get to work is the overall highest % complete. 

In the screenshot I have colored the cell I am trying to get in red.  The % complete should only take into account those rows that are orange as the others are NA.  

The formula I am using on the orange lines are:  =IFERROR((AVG(COLLECT(CHILDREN(), CHILDREN(Status@row), <>"N/A"))), "0")

I thought that using this same formula at the highest row would give me the overall % complete.  

Hoping you can help!  Again I really appreciate how much time you have spent on this with me. 

 

Regards, Amy

amy pelayo

now the screenshot

Paul Newcome

Remove the IFERROR statement (including the end portion) so that it is only the AVG/COLLECT. What error are you getting?