RAG Formula help

NicolaWheatley

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")))

KPH

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.