Automating Status Balls to Showcase Overall Health

Automating Status Balls to Showcase Overall Health

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

Answers

  • 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

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


    Thanks,

    Susie

  • 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?

Sign In or Register to comment.