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

BArmendariz

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.

Debbie Sawyer

@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

Itai Perez

Hey @BArmendariz

Can you share some screenshots please?

Paul Newcome

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

Debbie Sawyer

@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

BArmendariz

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

BArmendariz

@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

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

BArmendariz

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

@BArmendariz

AWESOME Formula!

Well done!

stevediaz

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.