Rolling Negative Status Up Hierarchy from Children

Jarret.Birdwell

Hello,

I am working to streamline my project timelines for quicker daily tracking.

I currently have a formula based on the task completion % and end date that gives a status and conditional formatting for the negative statuses: "At Risk" & "Late". It works great at the lowest level, but I am running into issues where I have to get to the lowest level to see if any task is "At Risk" or "Late". It wasn't an issue when I had only a few concurrent projects and levels; now that my scope has increased I find that I have to open every individual level in every project to manage risk. This is becoming a hot item for me to ensure long lead items are being ordered on schedule for projects that won't start in earnest for multiple months.

I have been working in a test environment to add functionality to roll the worst status up through the parents so I can more easily manage risk. I have a successful formula, when there is no references to a column formula and the formula is not a column formula.

Below are the formulas used and the test environment examples:

Current Status Formula: =IF(OR(AND([% Complete]@row = 0, NETWORKDAY(TODAY(), [End Date]@row) > 0, NETWORKDAY(TODAY(), [End Date]@row) < 30), AND([% Complete]@row > 0, [% Complete]@row < 1, NETWORKDAY(TODAY(), [End Date]@row) > 0, NETWORKDAY(TODAY(), [End Date]@row) < 10)), "At Risk", IF(AND([% Complete]@row < 1, [End Date]@row < TODAY()), "Late", IF([% Complete]@row = 1, "Complete", IF([% Complete]@row > 0, "In Progress", "Not Started"))))

Test Code For Roll Up (Column "S"): =IF([Child Count]@row = 0, IF(OR(AND([% Complete]@row = 0, NETWORKDAY(TODAY(), [End Date]@row) > 0, NETWORKDAY(TODAY(), [End Date]@row) < 30), AND([% Complete]@row > 0, [% Complete]@row < 1, NETWORKDAY(TODAY(), [End Date]@row) > 0, NETWORKDAY(TODAY(), [End Date]@row) < 10)), "At Risk", IF(AND([% Complete]@row < 1, [End Date]@row < TODAY()), "Late", IF([% Complete]@row = 1, "Complete", IF([% Complete]@row > 0, "In Progress", "Not Started")))), IF(COUNTIF(CHILDREN(S@row), "late") > 0, "Late", IF(COUNTIF(CHILDREN(S@row), "At Risk") > 0, "At Risk", IF(COUNTIF(CHILDREN(S@row), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(S@row), "Complete") > 0, "Complete", 0)))))

Test Codes for Column Formula References:

1. =INDEX(CHILDREN(Status@row), MATCH("Late", CHILDREN(Status@row), 0)) -BROKEN
2. =INDEX(CHILDREN([% Complete]120), MATCH("Late", CHILDREN([% Complete]120), 0)) -WORKS
3. =IF(INDEX(CHILDREN(S120), MATCH("Late", CHILDREN(S120), 0)) = "late", "Late", IF(INDEX(CHILDREN(S120), MATCH("At Risk", CHILDREN(S120), 0)) = "At Risk", "At Risk", IF(INDEX(CHILDREN(S120), MATCH("complete", CHILDREN(S120), 0)) = "complete", "Complete", 0))) -WORKS

The goal is the have "Item 1" (in this example) show a status of "Late" since it has multiple late tasks.

Is there a way to roll the status up the hierarchy that works with/as column formulas? Adding helper columns isn't an issue if the formula for them isn't also taxing; the target use is in a timeline with over 7000 lines and is already very slow.

Matt Lynn ACT

@Jarret.Birdwell Great job laying all this out, but a quick question to simplify my understanding:
Are you also saying that if Item 1 has ANY late items/children, it should show late, if none are late but ANY are at risk, then the parent is as risk etc etc? So worse case to best case, and the threshold for each stage is any? IF so.. I've done this many times….

Here's what you need:
1.) Add two hidden helper columns. (I do this on EVERY sheet). One is named "A", and the other is "C". They should have these formulas: =count(ancestors()) in "A" and =count(children()) in "C". These are column formulas. (This formula doesn't use the C… I just always include it)

2.) Change your status column formula to:
=IF(A@row = 0, IF(COUNTIF(DESCENDANTS(), "Late") > 0, "Late", IF(COUNTIF(DESCENDANTS(), "At Risk") > 0, "At Risk", IF(COUNTIF(DESCENDANTS(), "In Progress") > 0, "In Progress", IF(COUNTIF(DESCENDANTS(), "Complete") > 0, "Complete")))), IF(OR(AND([% Complete]@row = 0, NETWORKDAY(TODAY(), [End Date]@row) > 0, NETWORKDAY(TODAY(), [End Date]@row) < 30), AND([% Complete]@row > 0, [% Complete]@row < 1, NETWORKDAY(TODAY(), [End Date]@row) > 0, NETWORKDAY(TODAY(), [End Date]@row) < 10)), "At Risk", IF(AND([% Complete]@row < 1, [End Date]@row < TODAY()), "Late", IF([% Complete]@row = 1, "Complete", IF([% Complete]@row > 0, "In Progress", "Not Started")))))

This also works for multi level parent/child setups.

Also… to keep your today formula up to date:
Add a "TodayHelper" hidden helper date column. Then add an automation to record a date in that column every day at 4am or something early.

Matt Lynn ACT

@Jarret.Birdwell Great job laying all this out, but a quick question to simplify my understanding:
Are you also saying that if Item 1 has ANY late items/children, it should show late, if none are late but ANY are at risk, then the parent is as risk etc etc? So worse case to best case, and the threshold for each stage is any? IF so.. I've done this many times….

Here's what you need:
1.) Add two hidden helper columns. (I do this on EVERY sheet). One is named "A", and the other is "C". They should have these formulas: =count(ancestors()) in "A" and =count(children()) in "C". These are column formulas. (This formula doesn't use the C… I just always include it)

2.) Change your status column formula to:
=IF(A@row = 0, IF(COUNTIF(DESCENDANTS(), "Late") > 0, "Late", IF(COUNTIF(DESCENDANTS(), "At Risk") > 0, "At Risk", IF(COUNTIF(DESCENDANTS(), "In Progress") > 0, "In Progress", IF(COUNTIF(DESCENDANTS(), "Complete") > 0, "Complete")))), IF(OR(AND([% Complete]@row = 0, NETWORKDAY(TODAY(), [End Date]@row) > 0, NETWORKDAY(TODAY(), [End Date]@row) < 30), AND([% Complete]@row > 0, [% Complete]@row < 1, NETWORKDAY(TODAY(), [End Date]@row) > 0, NETWORKDAY(TODAY(), [End Date]@row) < 10)), "At Risk", IF(AND([% Complete]@row < 1, [End Date]@row < TODAY()), "Late", IF([% Complete]@row = 1, "Complete", IF([% Complete]@row > 0, "In Progress", "Not Started")))))

This also works for multi level parent/child setups.

Also… to keep your today formula up to date:
Add a "TodayHelper" hidden helper date column. Then add an automation to record a date in that column every day at 4am or something early.