Automating Status Balls to Showcase Overall Health

Shay Baumhauer

Hey There,

I am hoping to find a formula that will show the g/y/r status balls "overall health" based on the percentage of green, yellow or red balls in the column. Is there a way to "average" that? For example, if we have an equal amount of red and green balls the overall status ball would show yellow.

Thanks in advance

Genevieve P.

Hi Shay,

You can do this by using a COUNTIF formula to count each colour, then create rules based on which is larger. Try something like this:

=IF(OR(COUNTIF(Status:Status, "Red") >= (COUNTIF(Status:Status, "Green") + COUNTIF(Status:Status, "Yellow")), (COUNTIF(Status:Status, "Red") + COUNTIF(Status:Status, "Yellow")) >= COUNTIF(Status:Status, "Green")), "Red", IF(OR(COUNTIF(Status:Status, "Yellow") >= (COUNTIF(Status:Status, "Green") + COUNTIF(Status:Status, "Red")), COUNTIF(Status:Status, "Red") = COUNTIF(Status:Status, "Green"), COUNTIF(Status:Status, "Red") = COUNTIF(Status:Status, "Yellow"), COUNTIF(Status:Status, "Green") = COUNTIF(Status:Status, "Yellow")), "Yellow", IF(COUNTIF(Status:Status, "Green") >= (COUNTIF(Status:Status, "Red") + COUNTIF(Status:Status, "Yellow")), "Green", "Green")))

Here's what it says broken down:

1 . Red Rules:

=IF(OR(COUNTIF(Status:Status, "Red") >= (COUNTIF(Status:Status, "Green") + COUNTIF(Status:Status, "Yellow")), (COUNTIF(Status:Status, "Red") + COUNTIF(Status:Status, "Yellow")) >= COUNTIF(Status:Status, "Green")), "Red",

If there are more Red status balls (or equal to) than the number of Green and Yellow balls put together, return Red.

or

If the total of both Red and Yellow balls is greater than or equal to the number of Green balls, return Red.

(Keep in mind that this will return "Red" even if there are only Yellow and Green balls, but more Yellow than Green)

2 . Yellow Rules:

IF(OR(COUNTIF(Status:Status, "Yellow") >= (COUNTIF(Status:Status, "Green") + COUNTIF(Status:Status, "Red")), COUNTIF(Status:Status, "Red") = COUNTIF(Status:Status, "Green"), COUNTIF(Status:Status, "Red") = COUNTIF(Status:Status, "Yellow"), COUNTIF(Status:Status, "Green") = COUNTIF(Status:Status, "Yellow")), "Yellow",

There are four rules here that will return a Yellow Ball:

If there are more (or equal to) Yellow balls than Red or Green put together.

OR If there are the same number of Red and Green balls.

OR If there are the same number of Red and Yellow balls.

OR If there are the same number of Green and Yellow balls.

3 . Green Rules:

IF(COUNTIF(Status:Status, "Green") >= (COUNTIF(Status:Status, "Red") + COUNTIF(Status:Status, "Yellow")), "Green", "Green")))

If there are more Green balls than Red and Yellow put together, return Green. If none of the rules above apply, return Green.

Alternate Option:

Instead of building out this massive formula, you might want to consider creating 4 smaller formulas. One to count Red, one to count Yellow, one to count Green, and then one to compare the totals and return a specific colour based on that. The fourth formula would be similar to the one above (a Nested IF statement), but it since you're removing the embedded "COUNTIF" portion of it, should you need to change anything in the future it might be easier to read. Let me know if you would like to see an example of this and I'm happy to help explain further.

Help Center Articles

Here are some articles I used to help build this: IF Function / OR Function / COUNTIF Function/ Formula Operators

Susie Wachowski

How would I build this to include a "grey" colored ball?

Thanks,

Susie

Genevieve P.

Hi @Susie Wachowski

I would suggest going with my Alternate Option at the end of the post, if you're looking to use the gray ball as well.

You can just build one formula per ball that's like this:

=COUNTIF(Status:Status, "Green")

then in another cell:

=COUNTIF(Status:Status, "Gray")

Etc... make sure you spell Gray with an A or it won't recognize it and count 0.

Then in your final 5th cell you can set up your rules for what to do, depending on what's showing. Can you go into a bit more detail of what you're looking to do? Do you just want to display what status has the most total numbers?

Carla H

@Genevieve P where in the formula would I include a range of cells?

My sheet has 3 indented sections and I want to do overall health in the summary row of each section

Paul Newcome

@Carla H Wherever you see "Status:Status" in @Genevieve P's solution, replace it with "CHILDREN()" (no quotes).

Carla H

thanks @Paul Newcome you saved me again!!

Paul Newcome

@Carla H Happy to help! 👍️

Lori Drum

@Genevieve P So very helpful! Thank you! What if you want to do that same action, but restrict it to counting rows that have "Phase" in the column named Type?

Genevieve P.

Hi @Lori Drum

In this instance you would use COUNTIFS (with an S) and add in that criteria and column into each of the statements.

For example:

=IF(OR(COUNTIFS(Status:Status, "Red", Type:Type, "Phase") >= (COUNTIFS(Status:Status, "Green", Type:Type, "Phase")...etc

Let me know if this works for you. 🙂

Cheers,

Genevieve

Kate Kelley

Hi @Paul Newcome & @Genevieve P. ,

I am desperately trying to do the same thing here but something with the formula is giving me skewed results. Below is the formula that I tried to modify using the above suggestions. I'd also love to include the Gray ball as well. I have sheet that I based off of one of the templates out there because it looked as if that would do what I wanted. When I started to clear the start/end dates, it messed with the color coding and hierarchy ball colors. When I added the modified formula below, it does not seem to count correctly. If all the children balls are green, the parent row switches to yellow.

Ideally, I'd love to have each subset be averaged and then have an overall health status based on all the rows below. Is this even possible? We're trying to do a simple project tracker and this is hanging me up.

Modified formula using suggested above: =IF(OR(COUNTIFS(Children(), "Red") >= (COUNTIFS(Children(), "Green") + COUNTIFS(Children(), "Yellow")), (COUNTIFS(Children(), "Red") + COUNTIFS(Children(), "Yellow")) >= COUNTIFS(Children(), "Green")), "Red", IF(OR(COUNTIFS(Children(), "Yellow") >= (COUNTIFS(Children(), "Green") + COUNTIFS(Children(), "Red")), COUNTIFS(Children(), "Red") = COUNTIFS(Children(), "Green"), COUNTIFS(Children(), "Red") = COUNTIFS(Children(), "Yellow"), COUNTIFS(Children(), "Green") = COUNTIFS(Children(), "Yellow")), "Yellow", IF(COUNTIFS(Children(), "Green") >= (COUNTIFS(Children(), "Red") + COUNTIFS(Children(), "Yellow")), "Green", "Green")))

Row formula that was included in template: =IF(Hierarchy@row = 1, IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(AND(COUNTIF(CHILDREN(), "Red") = 0, COUNTIF(CHILDREN(), "Yellow") > 0), "Yellow", IF(AND(COUNTIF(CHILDREN(), "Yellow") = 0, COUNTIF(CHILDREN(), "Green") > 0), "Green", "Gray"))), IF(Status@row = "Complete", "Green", IF(AND(Status@row <> "Complete", TODAY() > [End Date]89), "Red", IF(AND(Status@row = "Not Started"), "Gray", "Yellow"))))

Thank you!!

Genevieve P.

Hi @Kate Kelley

What helps me with formulas is breaking down each individual IF statement to read what the logic is. Any time there's an IF, separate that out. Then we can look at the order of the statements and make sure none of them are blocking each other.

Your Red Statement:

=IF(OR(COUNTIFS(Children(), "Red") >= (COUNTIFS(Children(), "Green") + COUNTIFS(Children(), "Yellow")), (COUNTIFS(Children(), "Red") + COUNTIFS(Children(), "Yellow")) >= COUNTIFS(Children(), "Green")), "Red",

In words:

• If there are More Red Children than there are Green and Yellow combined
• OR if there are more Red and Yellow Children than there are Green in total, return Red.

So far so good!

Your Yellow Statement:

IF(OR(COUNTIFS(Children(), "Yellow") >= (COUNTIFS(Children(), "Green") + COUNTIFS(Children(), "Red")), COUNTIFS(Children(), "Red") = COUNTIFS(Children(), "Green"), COUNTIFS(Children(), "Red") = COUNTIFS(Children(), "Yellow"), COUNTIFS(Children(), "Green") = COUNTIFS(Children(), "Yellow")), "Yellow",

• If there are more Yellow Children than Green + Red,
• OR if the number of Red and Green Children are Equal,
• OR if the number of Red and Yellow Children are Equal (note, this is why you're getting Yellow for all Green rows. Red and Yellow both = 0 in this case)
• OR if the number of Yellow and Green Children are Equal, return Yellow.

Your Green Statement:

IF(COUNTIFS(Children(), "Green") >= (COUNTIFS(Children(), "Red") + COUNTIFS(Children(), "Yellow")), "Green", "Green")))

• If there are more Green children than Red + Yellow, return Green

It looks like what's getting in your way is the order of the statements. We'll want to put Green first, so that it first looks for Green Children. This will overwrite the OR statement in your Yellow one that's giving you Yellow when the whole thing is Green.

Next, let's do the Yellow Statement. This is because the RED statement says "if there are more Red and Yellow Children than there are Green in total, return Red." This means that if you have ONLY Yellow Children, the top will turn Red, not Yellow, so we want this to be the last thing to check after the Yellow criteria.

Here's the exact same formula and statements, just rearranged!

=IF(COUNTIFS(CHILDREN(), "Green") >= (COUNTIFS(CHILDREN(), "Red") + COUNTIFS(CHILDREN(), "Yellow")), "Green", IF(OR(COUNTIFS(CHILDREN(), "Yellow") >= (COUNTIFS(CHILDREN(), "Green") + COUNTIFS(CHILDREN(), "Red")), COUNTIFS(CHILDREN(), "Red") = COUNTIFS(CHILDREN(), "Green"), COUNTIFS(CHILDREN(), "Red") = COUNTIFS(CHILDREN(), "Yellow"), COUNTIFS(CHILDREN(), "Green") = COUNTIFS(CHILDREN(), "Yellow")), "Yellow", IF(OR(COUNTIFS(CHILDREN(), "Red") >= (COUNTIFS(CHILDREN(), "Green") + COUNTIFS(CHILDREN(), "Yellow")), (COUNTIFS(CHILDREN(), "Red") + COUNTIFS(CHILDREN(), "Yellow")) >= COUNTIFS(CHILDREN(), "Green")), "Red", "Green")))

The Gray status is a bit trickier; do you want these rows to be skipped completely? I would maybe suggest adding just one statement that says if all the Children are Gray, return Gray, otherwise ignore these cells in the rollup?

=IF(COUNTIFS(CHILDREN(), "Gray") = COUNT(CHILDREN()), "Gray", IF(COUNTIFS(CHILDREN(), "Green") >= (COUNTIFS(CHILDREN(), "Red") + COUNTIFS(CHILDREN(), "Yellow")), "Green", IF(OR(COUNTIFS(CHILDREN(), "Yellow") >= (COUNTIFS(CHILDREN(), "Green") + COUNTIFS(CHILDREN(), "Red")), COUNTIFS(CHILDREN(), "Red") = COUNTIFS(CHILDREN(), "Green"), COUNTIFS(CHILDREN(), "Red") = COUNTIFS(CHILDREN(), "Yellow"), COUNTIFS(CHILDREN(), "Green") = COUNTIFS(CHILDREN(), "Yellow")), "Yellow", IF(OR(COUNTIFS(CHILDREN(), "Red") >= (COUNTIFS(CHILDREN(), "Green") + COUNTIFS(CHILDREN(), "Yellow")), (COUNTIFS(CHILDREN(), "Red") + COUNTIFS(CHILDREN(), "Yellow")) >= COUNTIFS(CHILDREN(), "Green")), "Red", "Green"))))

Cheers,

Genevieve