Need Help with Formula to Roll Up and Average Schedule Health.

Need Help with Formula to Roll Up and Average Schedule Health. 

I have one that now updates the Schedule Health RYG based on the Start Date/End Date and Task Status.

Next, I am trying to roll up/average those Individual Task schedule health values to an overall Phase schedule health value and then ultimately to the Overall Project schedule health.

Current formula: Overall Project Schedule Health would never move from Green unless we were past the final End Date

Baseline model: Once you go red, you can never get back to green or even yellow

Any insight would be much appreciated.

Tags:

Best Answer

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭
    Answer ✓

    @BArmendariz

    You will need to write a nested IF function to deal with the levels of hierarchy in your RAG column.

    You will have 1 set of IF's to deal with the Child Rows (usually based on Date and % complete etc)

    Then another set of IF's to deal with Parent Rows (usually based on if there is a Red ball in the children then the parent is red, if there is a yellow one, then parent is yellow etc)

    If the Project level (row1) logic needs to be different then a set of IF's for that will also be required.

    For example:

    =IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNTIF(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue", IF(COUNTIF(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", "Yellow"))), IF([% Complete]@row = 1, "Green", IF([End Date]@row < TODAY(), "Red", IF([% Complete]@row = 0, "Blue", "Yellow"))))


    The first half of this is dealing with Parent Rows:

    =IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNTIF(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue", IF(COUNTIF(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", "Yellow")))

    The opening condition: =IF(COUNT(CHILDREN([Task Name]@row)) > 0, is asking if there are any children (i.e. is this a Parent row) then this particular formula is then saying if it is a parent row then, if all the children are Blue then the Parent is also Blue, if all the children are Green then the Parent is also Green, if ANY of the children are Red then the Parent is also Red, or for anything else the Parent is Yellow.

    The second half is child rows:

    , IF([% Complete]@row = 1, "Green", IF([End Date]@row < TODAY(), "Red", IF([% Complete]@row = 0, "Blue", "Yellow"))))

    If the row is NOT a parent row then if % Complete = 100% then Green, if the row is Overdue then Red, if the % Complete is still 0 then Blue otherwise it is set to Yellow.

    I hope this helps...

    Kind regards

    Debbie

Answers

  • Itai
    Itai ✭✭✭✭✭✭

    Hey @BArmendariz

    Can you share some screenshots please?

    Itai Perez

    Project Manager | Transformation Department

    Gong cha

    If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂

    https://www.linkedin.com/in/itai-perez-740543116/

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Screenshots and your existing formula(s) would be very helpful.

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭
    Answer ✓

    @BArmendariz

    You will need to write a nested IF function to deal with the levels of hierarchy in your RAG column.

    You will have 1 set of IF's to deal with the Child Rows (usually based on Date and % complete etc)

    Then another set of IF's to deal with Parent Rows (usually based on if there is a Red ball in the children then the parent is red, if there is a yellow one, then parent is yellow etc)

    If the Project level (row1) logic needs to be different then a set of IF's for that will also be required.

    For example:

    =IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNTIF(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue", IF(COUNTIF(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", "Yellow"))), IF([% Complete]@row = 1, "Green", IF([End Date]@row < TODAY(), "Red", IF([% Complete]@row = 0, "Blue", "Yellow"))))


    The first half of this is dealing with Parent Rows:

    =IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNTIF(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue", IF(COUNTIF(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", "Yellow")))

    The opening condition: =IF(COUNT(CHILDREN([Task Name]@row)) > 0, is asking if there are any children (i.e. is this a Parent row) then this particular formula is then saying if it is a parent row then, if all the children are Blue then the Parent is also Blue, if all the children are Green then the Parent is also Green, if ANY of the children are Red then the Parent is also Red, or for anything else the Parent is Yellow.

    The second half is child rows:

    , IF([% Complete]@row = 1, "Green", IF([End Date]@row < TODAY(), "Red", IF([% Complete]@row = 0, "Blue", "Yellow"))))

    If the row is NOT a parent row then if % Complete = 100% then Green, if the row is Overdue then Red, if the % Complete is still 0 then Blue otherwise it is set to Yellow.

    I hope this helps...

    Kind regards

    Debbie

  • @Paul Newcome

    Here is my original formula. This one works as expected, just looking for a rollup at the phase level.

    =IF(AND([Start Date]@row > TODAY(), Status@row = "Not Started"), "Gray", IF(OR(AND([% Complete]@row <= 0.25, [End Date]@row >= TODAY(), [End Date]@row <= TODAY(7)), AND([% Complete]@row <> 1, [End Date]@row < TODAY())), "Red", IF(AND([% Complete]@row <= 0.5, [End Date]@row >= TODAY(), [End Date]@row <= TODAY(7)), "Yellow", IF(AND([% Complete]@row >= 0.75, [End Date]@row >= TODAY(), [End Date]@row <= TODAY(7)), "Green", "Green"))))

  • @Debbie Sawyer

    Thank you, trying now. I had tried a few things, but hadn't considered the word "children." I'll come back and let everyone know how it works out.

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭

    To be honest, I pop in a helper column called Parent which is a checkbox, then I simplify the formula to say:

    =IF(Parent@row =1, insert nested IF's here for Parent logic, insert nested IF's here for Child logic))

    Good luck!

    Kind regards

  • This is what we ended up with, and it worked beautifully.

    =IF(COUNT(CHILDREN()) > 0, IF(COUNTIF(CHILDREN(), "Gray") = COUNT(CHILDREN()), "Gray", IF((COUNTIF(CHILDREN(), "Yellow") + (COUNTIF(CHILDREN(), "Green") * 2)) / COUNTIF(CHILDREN(), <>"Gray") <= 0.5, "Red", IF((COUNTIF(CHILDREN(), "Yellow") + (COUNTIF(CHILDREN(), "Green") * 2)) / COUNTIF(CHILDREN(), <>"Gray") <= 1.5, "Yellow", IF((COUNTIF(CHILDREN(), "Yellow") + (COUNTIF(CHILDREN(), "Green") * 2)) / COUNTIF(CHILDREN(), <>"Gray") <= 2.5, "Green", "Gray")))), IF([% Complete]@row >= 1, "Green", IF(OR([Start Date]@row > TODAY(), ISBLANK([End Date]@row)), "Gray", IF([End Date]@row < TODAY(), "Red", IF([End Date]@row < WORKDAY(TODAY(), 3), "Yellow", "Green")))))

    Then right-click on the column and select "Convert to Column Formula"

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭

    @BArmendariz

    AWESOME Formula!

    Well done!

  • Hello

    Use a weighted average approach to calculate overall project schedule health. Assign weights to tasks based on importance. Multiply task schedule health by weight for each task, then average for phases and overall project.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!