RAG Status based off Number Range

AHeadlee

Hi-

I came up with this formula to define RAG Risk Score based off a number or a range:

Green = Risk Score of 3 or less

Amber = Risk Score between 4 & 9

Red = Risk Score of 10 or more

=IF([Overall Risk Score]1 <= "3", "Green", IF([Overall Risk Score]1 = "4-9", "Yellow", IF([Overall Risk Score]1 >= "10", "Red")))

However, the coloring isn't matching the criteria. Example: a 7.5 is coming up as Red and not Amber.

Paul Newcome

Try this instead:

=IF([Overall Risk Score]1 <= 3, "Green", IF([Overall Risk Score]1 >= 10, "Red", "Yellow"))

Paul Newcome

@John_Foster It is also the "3" and "10" because the quotes are causing the formula to look for text strings. You can't be less than or greater than a text string. Removing the quotes from the numbers being used (and adjusting for the yellow) are both needed.

Paul Newcome

Try this instead:

=IF([Overall Risk Score]1 <= 3, "Green", IF([Overall Risk Score]1 >= 10, "Red", "Yellow"))

John_Foster

Hi @AHeadlee,

I believe this is because you have used 4-9 as the value for Yellow, which it does not like, I would change the formula to the following.

=IF([Overall Risk Score]@row <=3,"Green",IF([Overall Risk Score]@row >=10,"Red","Yellow"))

This only uses checks for the under or equal to 3 and over or equal to 10, otherwise everything in between should be yellow.

Hope this helps!

John

Paul Newcome

@John_Foster It is also the "3" and "10" because the quotes are causing the formula to look for text strings. You can't be less than or greater than a text string. Removing the quotes from the numbers being used (and adjusting for the yellow) are both needed.

John_Foster

Thanks @Paul Newcome for clarifying it, I did notice the quotes being used which is why I had not included them in mine as it should recognise numeric values, but was not sure that would have impacted the formula.

AHeadlee

Thank you @John_Foster and @Paul Newcome! This worked. Appreciate the help.

Paul Newcome

Happy to help. 👍️