# Formula for RAG Status

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

• 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"))), "")

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

• 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! 😊

When I added both the formulas above, I get this 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?

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