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
 10.8K Get Help
 65 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 56 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!