Formula for RAG Status

Safiya B

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

Nick Korna

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

@Nick Korna

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

Nick Korna

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

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

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

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

Nick Korna

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

@Nick Korna

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