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"))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
Try this instead:
=IF([Overall Risk Score]1 <= 3, "Green", IF([Overall Risk Score]1 >= 10, "Red", "Yellow"))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!