RAG Formula help
I am trying to get my formula to work for an overall RAG based off five RAG statuses.
I have got the following correct:
If 1 Red and 1 amber shows amber
If 2 + Reds show Red
If 1 amber + 4 greens shows Green.
However I also need it to be if it is 1 Red and the other 4 are green it should show amber and it is currently showing as red. Please can anyone help with what I need to do?
=IF(COUNTIF(CAPEX@row:Timeline@row, "Red") > 1, "Red", IF(COUNTIF(CAPEX@row:Timeline@row, "Red") > 0, IF(COUNTIF(CAPEX@row:Timeline@row, "Yellow") > 0, "Yellow", "Red"), IF(COUNTIF(CAPEX@row:Timeline@row, "Yellow") > 1, "Yellow", "Green")))
Answers
-
I think we can simplify this a little to make it easier to edit. If you want to show as Green if there are 4 greens, regardless of whether the other one is red or yellow you could do that in one step.
The logic is:
If count Red greater than one then "Red"
--- If false, if count Red greater than 0 and count yellow greater than zero, then "Yellow"
------ If false, if count Green greater than 4, then "Green"
--------- If false, Blue*
* I like to use the 4 color symbols in the formula column so I can capture any rows that do not meet my logic. A blue row will be more obvious and make troubleshooting easier.
This formula will do the above
=IF(COUNTIF(CAPEX@row:Timeline@row, "Red") > 1, "Red", IF(AND(COUNTIF(CAPEX@row:Timeline@row, "Red") > 0, (COUNTIF(CAPEX@row:Timeline@row, "Yellow") > 0)), "Yellow", IF(COUNTIF(CAPEX@row:Timeline@row, "Green") > 3, "Green", "Blue")))
Let me know how you get on.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!