Scheduled Health Rollup using "Red Yellow Green" formula

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

All comments

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

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

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

Ray Lindstrom

@rachkh,

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

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!

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

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

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

I'm using this for my individual tasks:

If that helps.

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

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

@Genevieve P.

Hopefully this is more helpful ;)

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?

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