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 = "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.
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 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
-
@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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!