Scheduled Health Rollup using "Red Yellow Green" formula

rachkh

Question. I am using this formula for my individual task health:

=IF([% Complete]@row = 1, "Green", IF([Start Date]@row > TODAY(), "Green", IF(AND([End Date]@row <= TODAY(), [Start Date]@row < TODAY()), "Red", IF(NOT([Start Date]@row > TODAY(+7)), "Yellow", IF([Start Date]@row <= TODAY(), "Green")))))

Works great!

I would like to have a rollup / summary of my overall task health for the project. How would I do that formula?

I found this:

=IF(COUNTIFS(CHILDREN(), "Red") > 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green")))

Would that work?

978FD6B8-45F4-4106-A24D-B521DE126740.png

Ray Lindstrom

@rachkh,

The metric you show is exactly why I mentioned losing the ability to use discretion when a Health roll-up formula is in place. Many times folks don't want their roll-up to be forced. Someone may have a yellow item or two that's just not important enough to cause the project to roll-up to yellow.

As far as a better equation: I don't know of a better one myself. I would be using the one you posted, but would couple it with the automation otherwise the roll-up will break once a row becomes a parent row. Unless of course you manually add the formula every time a row becomes a parent, but that manual effort and need to remember is not preferred or scalable.

Remember automation runs regularly (every couple minutes or so), so you won't see the formula appear instantly when a row becomes a parent.

BRgds,

-Ray

Genevieve P.

Hi @rachkh

If you want it to look at all rows equally, you could use the DESCENDANTS Function instead of CHILDREN:

=IF(COUNTIFS(DESCENDANTS(), "Red") > 2, "Red", IF(COUNTIFS(DESCENDANTS(), "Yellow") > 4, "Yellow", IF(COUNTIFS(DESCENDANTS(), "Green") = COUNT(DESCENDANTS()), "Green", "Green")))

Or you could add the previous CHILDREN formula in to some of your other top-level rows as well (e.g. the Grey ones).

Cheers,

Genevieve

Ray Lindstrom

Hi @rachkh,

Yes, that formula will allow for the Health roll-up. This formula will have to be applied to each parent row's Health column.

Also, if you use a Level column or a Children Count column, you could use automation to enter your roll-up formula into the Health column for each time a row becomes a parent row (and can remove it automatically when the row becomes no longer a parent row). You can't select "when a row becomes a parent" via the automation features, but you can use the Level column value to determine if the row is a parent row.

Just a side thought: The only thing this doesn't allow is for discretion to be used in reporting overall Health for a project. One tiny Yellow will cause the entire project to go Yellow. Often times these small Yellows aren't weighted enough for some people to want to change their project's overall Health.

Hope this helps!

BRgds,

-Ray

rachkh

Thanks @Ray Lindstrom!

I appreciate your explanation - is there a better equation that I should use? I have that metric to pull to my project dashboard. But as you said ... maybe there is a better way? Here is a snip of my dashboard:

Best,

@rachkh

8641DA8D-817A-4465-9EDB-9AB5000490E1.png

Ray Lindstrom

@rachkh,

The metric you show is exactly why I mentioned losing the ability to use discretion when a Health roll-up formula is in place. Many times folks don't want their roll-up to be forced. Someone may have a yellow item or two that's just not important enough to cause the project to roll-up to yellow.

As far as a better equation: I don't know of a better one myself. I would be using the one you posted, but would couple it with the automation otherwise the roll-up will break once a row becomes a parent row. Unless of course you manually add the formula every time a row becomes a parent, but that manual effort and need to remember is not preferred or scalable.

Remember automation runs regularly (every couple minutes or so), so you won't see the formula appear instantly when a row becomes a parent.

BRgds,

-Ray

rachkh

So ... on further thought. Is there a way to add to my existing rollup formula (see below):

=IF(COUNTIFS(CHILDREN(), "Red") > 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green")))

that adds:

If 5 or more tasks are yellow turn “yellow”

If 3 or more tasks are red turn “red” 

91% of my tasks are "Green" so when I only have one "red" and it turns the overall project “red” it isn’t a clear picture of health. 

How would I edit my current formula to do that?

Any ideas?

Thanks!

Genevieve P.

Hi @rachkh

You can update what each COUNT is looking for. Right now you're simply looking for if the COUNT of each colour is "greater than 0" (so there's 1).

Instead, you can change this to look for a different count:

=IF(COUNTIFS(CHILDREN(), "Red") > 2, "Red", IF(COUNTIFS(CHILDREN(), "Yellow") > 4, "Yellow", IF(COUNTIFS(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", "Green")))

Note that this means if you have 2 Red tasks and 3 Yellow tasks and only 1 Green task, the default at the end is Green. I may suggest having your default be Yellow, if not all of the children are Green:

=IF(COUNTIFS(CHILDREN(), "Red") > 2, "Red", IF(COUNTIFS(CHILDREN(), "Yellow") > 4, "Yellow", IF(COUNTIFS(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", "Yellow")))

Cheers,

Genevieve

rachkh

@Genevieve P. Thanks for commenting!! I appreciate any help I can get. So I tried your formula. I think something is off.

I tried this one, because my default is Green.

=IF(COUNTIFS(CHILDREN(), "Red") > 2, "Red", IF(COUNTIFS(CHILDREN(), "Yellow") > 4, "Yellow", IF(COUNTIFS(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", "Green")))

It worked. But if you look at this screen shot. It is still returning "green" when you can see 7 "reds" shouldn't it return "red"

Screen Shot 2022-10-21 at 10.19.31 AM.png

I'm using this for my individual tasks:

=IF([% Complete]@row = 1, "Green", IF([Start Date]@row > TODAY(), "Green", IF(AND([End Date]@row <= TODAY(), [Start Date]@row < TODAY()), "Red", IF(NOT([Start Date]@row > TODAY(+7)), "Yellow", IF([Start Date]@row <= TODAY(), "Green")))))

If that helps.

Genevieve P.

Hi @rachkh

Would you mind posting the screen capture again, showing what rows are Parent rows? (Which ones have the + or - symbol in the Primary Column).

rachkh

Screen Shot 2022-10-21 at 10.27.17 AM.png

@Genevieve P.

Hopefully this is more helpful ;)

Genevieve P.

Hi @rachkh

Thank you, this definitely helps!

The CHILDREN function only looks at direct children of the current row.

For example in your image, there are only 2 Children for the Dark Blue Line: the 2 Grey Rows. These rows are Green and Yellow, therefore the Dark Blue row is Green.

Are you only placing the formula in that very top row? Or do you have it on each individual Parent row?

rachkh

@Genevieve P.

Well ... I feel dumb! I thought is was counting all the rows below. That makes total sense now.

Yes, I was only placing that formula in the top (dark blue) row as the rollup summary. Everything else I have been using:

=IF([% Complete]@row = 1, "Green", IF([Start Date]@row > TODAY(), "Green", IF(AND([End Date]@row <= TODAY(), [Start Date]@row < TODAY()), "Red", IF(NOT([Start Date]@row > TODAY(+7)), "Yellow", IF([Start Date]@row <= TODAY(), "Green")))))

Genevieve P.

Hi @rachkh

If you want it to look at all rows equally, you could use the DESCENDANTS Function instead of CHILDREN:

=IF(COUNTIFS(DESCENDANTS(), "Red") > 2, "Red", IF(COUNTIFS(DESCENDANTS(), "Yellow") > 4, "Yellow", IF(COUNTIFS(DESCENDANTS(), "Green") = COUNT(DESCENDANTS()), "Green", "Green")))

Or you could add the previous CHILDREN formula in to some of your other top-level rows as well (e.g. the Grey ones).

Cheers,

Genevieve

rachkh

Thanks @Genevieve P.!

It worked! You just made my day. Thanks a million!!!!

R.