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

amy pelayo
amy pelayo ✭✭✭✭
edited 12/09/19 in Formulas and Functions

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. 

 

Tags:

Comments

  • Ezra
    Ezra ✭✭✭

    Look at using the weighted average function..

  • Paul Newcome
    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
    Paul Newcome ✭✭✭✭✭✭

    Try something along the lines of

     

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

  • amy pelayo
    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
    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

    Pic.JPG

  • Genevieve P.
    Genevieve P. Employee Admin

    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
    amy pelayo ✭✭✭✭

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

  • amy pelayo
    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
    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
    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

    pic 1.JPG

    pic 2.JPG

  • Paul Newcome
    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
    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

    Pelayo.JPG

  • amy pelayo
    amy pelayo ✭✭✭✭

    now the screenshot

    Pelayo_0.JPG

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!