Working out RAG status based on 3 columns
Trying to summarise the RAG status in one field but unsure how I do that
Column D needs to make sure it groups Column A and then column B and then assign the lowest RAG from column C
For example, For Workstream 1, 'Pilot' appears twice in column B with the lowest RAG status of Red. So Column D automatically needs to assign Red to Workstream 1, pilot sub workstream.
If it is easier then RAG status could be assigned a number too. For example: Red becomes 1, Amber = 2, Green = 3, Complete = 4 etc
Table with dummy data below. Grateful for any assistance on this, please.
|