RAG Status based off Number Range
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 = "49", "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.
Best Answers

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

@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.
Answers

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

Hi @AHeadlee,
I believe this is because you have used 49 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

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

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.

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

Happy to help. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!