Formula for RAG Status

Options

Hello - I have a current formula that reads the following to show the Project Status on the left hand column as per the image below:

=IFERROR(IF(COUNTIF(CHILDREN(Status@row), "Red") / COUNT(CHILDREN(Status@row)) >= 0.2, "Red", IF(COUNTIF(CHILDREN(Status@row), "Yellow") / COUNT(CHILDREN(Status@row)) >= 0.2, "Yellow", "Green")), "")


My question is that some of the RAG status' are "Gray" (that shows not started) so in any given instance, at the "Project" level, they are all Gray which should then make the Project Status column Gray.

How do I do this??

I've tried the following formula but it hasn't seemed to work:

=IFERROR(IF(COUNTIF(CHILDREN(Status@row), "Red") / COUNT(CHILDREN(Status@row)) >= 0.2, "Red", IF(COUNTIF(CHILDREN(Status@row), "Yellow") / COUNT(CHILDREN(Status@row)) >= 0.2, "Yellow", IF(COUNTIF(CHILDREN(Status@row), "Green") / COUNT(CHILDREN(Status@row)) >= 0.81, "Green", "Gray")), "")

Thank you

Best Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    Options

    A fairly simple change, just change the percentage for green (shown in bold so you can tinker with it further if required):

    =IFERROR(IF(COUNTIF(CHILDREN(Status@row), "Red") / COUNT(CHILDREN(Status@row)) >= 0.2, "Red", IF(COUNTIF(CHILDREN(Status@row), "Yellow") / COUNT(CHILDREN(Status@row)) >= 0.2, "Yellow", IF(COUNTIF(CHILDREN(Status@row), "Green") / COUNT(CHILDREN(Status@row)) >= 0.6, "Green", "Gray"))), "")

  • Safiya B
    Safiya B ✭✭
    Answer ✓
    Options

    @Nick Korna

    Thank you once again for your assistance. You are a super star! It worked :).

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Options

    Hi @Safiya B,

    You're just missing a bracket in the formula. Try:

    =IFERROR(IF(COUNTIF(CHILDREN(Status@row), "Red") / COUNT(CHILDREN(Status@row)) >= 0.2, "Red", IF(COUNTIF(CHILDREN(Status@row), "Yellow") / COUNT(CHILDREN(Status@row)) >= 0.2, "Yellow", IF(COUNTIF(CHILDREN(Status@row), "Green") / COUNT(CHILDREN(Status@row)) >= 0.81, "Green", "Gray"))), "")

    However, this will possibly give you some unexpected results - if you have 2 non green and 8 green (out of 10 children) then the result will be gray. If this isn't want you're after, I would change the 0.81 to 0.8 to get this:

    =IFERROR(IF(COUNTIF(CHILDREN(Status@row), "Red") / COUNT(CHILDREN(Status@row)) >= 0.2, "Red", IF(COUNTIF(CHILDREN(Status@row), "Yellow") / COUNT(CHILDREN(Status@row)) >= 0.2, "Yellow", IF(COUNTIF(CHILDREN(Status@row), "Green") / COUNT(CHILDREN(Status@row)) >= 0.8, "Green", "Gray"))), "")

    Comparison:

    Pick and use whichever is best for you.

    Hope this helps; if you've any questions etc. then just ask! 😊

  • Safiya B
    Options

    Hi @Nick Korna

    Thanks for the reply.

    When I added both the formulas above, I get this error:

    Use column formulas to apply calculations to all rows in a sheet | Smartsheet Learning Center (link to above error)

    And the formulas (both of them) then change some of the Overall RAG status to gray even though there are significant #'s of greens?

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Options

    Breaking down the percentages in the formula by order of them appearing:

    If 20%+ red, status is red

    If 20%+ yellow, status is yellow

    If 81% (or 80%)+ green, then status is green

    Otherwise status is gray.

    If you're getting gray appearing too often, then I would bring down the percentage (maybe 60%?) on green to something more acceptable as currently it's only going to display green where it is the vast majority.

  • Safiya B
    Options

    Thanks Nick - please advise what the formula itself would then look like based on your comments above?

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    Options

    A fairly simple change, just change the percentage for green (shown in bold so you can tinker with it further if required):

    =IFERROR(IF(COUNTIF(CHILDREN(Status@row), "Red") / COUNT(CHILDREN(Status@row)) >= 0.2, "Red", IF(COUNTIF(CHILDREN(Status@row), "Yellow") / COUNT(CHILDREN(Status@row)) >= 0.2, "Yellow", IF(COUNTIF(CHILDREN(Status@row), "Green") / COUNT(CHILDREN(Status@row)) >= 0.6, "Green", "Gray"))), "")

  • Safiya B
    Safiya B ✭✭
    Answer ✓
    Options

    @Nick Korna

    Thank you once again for your assistance. You are a super star! It worked :).

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!