RAG Status

Isla McLean

Hi there,

I can see this has been asked a couple of times, but I can't quite work out how to apply the formulas to my sheet.

I have a RAG column, that I would like to tally up at the top of each section, and then an overall RAG status. The RAG status isn't currently related to the % complete, or the 'Project Status' (On Track, Overdue etc.) I like to keep my project plans quite manual as there are changes daily!

I have highlighted the cells (in yellow) where I would like the RAG 'average' to appear.

Thank you!

Jeff Reisman

@Isla McLean

The way I've done this in the past is to add a hidden helper column that assigns a numeric value based on the values in your RAG column. Let's call the column "RAGValue".

=IF(RAG@row = "Green", 3, IF(RAG@row = "Yellow", 2, IF(RAG@row = "Red", 1, "")))

Then, in the cells where you want the average, you'll calculate the average of those numeric RAGValues, and use that average to determine your ball color.

Are the blue rows set up as parent rows or just regular rows?

If they're parent rows:

=IF(AVG(CHILDREN(RAGValue@row)) >= 2.5, "Green", IF(AVG(CHILDREN(RAGValue@row)) >= 1.5, "Yellow", IF(AVG(CHILDREN(RAGValue@row)) < 1.5, "Red", "")))

If they're just regular rows, you'll need to specify the RAGValue range for each AVG, and it will change for each cell where you want to get the average RAG value. This would handle the average for rows 6-9:

=IF(AVG(RAGValue6:RAGValue9) >= 2.5, "Green", IF(AVG(RAGValue6:RAGValue9) >= 1.5, "Yellow", IF(AVG(RAGValue6:RAGValue9) < 1.5, "Red", "")))