RAG reporting help

[Excelpleb]

Hi there, I'm hopeful someone may be able to tell me this is possible, and if so, how!
I have a new spreadsheet I'm building to better amalgamate KPI results and RAG reporting. I am setting conditional formatting thresholds for each KPI and these are reported monthly over a 12 month period. The bit I would like to work and can't figure a way is an overall RAG based on those monthly results. I've set frozen panes at the start of the spreadsheet to indicate a status of each KPI. We currently have an overall marker status on a rolling 3 month, so all green = green, 2 green, 1 red = green, 2 red, 1 green = amber, 3 red = red. Is there any way of doing this or will this have to remain a manual entry?

 

[awoohaw]

Many folks in the forum may not know what KPI and RAG are. I have no idea what RAG is.
Mr. Excel has a tool called xl2bb add in (link below) that allows you to share your data as a mini workbook.
If you cannot use that, then please post your data as a TABLE.

You need to explain the rules for making a cell green / red / amber in the first place.
And yes, it can probably be done without manual entry, but you need to give a lot more information.

 

[awoohaw]

Not really sure how to go about your numbers. But, if you have formulas that create a monthly Red or Green value. Then use this to try to fit into your scenario:

Book1
	A	B	C
1		Current Status	Amber
2		KPI Score
3	2023-01-01	Red
4	2023-02-01	Green
5	2023-03-01	Green
6	2023-04-01	Green
7	2023-05-01	Red
8	2023-06-01	Green
9	2023-07-01	Red
10	2023-08-01	Red
11	2023-09-01	Red
12	2023-10-01	Green
13	2023-11-01	red
14	2023-12-01	red
15	2024-01-01	Green
16	2024-02-01
17	2024-03-01
Sheet1

Cell Formulas
Range	Formula
C1	C1	=LET(last3,TAKE(FILTER(B3:B17,(B3:B17<>""),""),-3), CHOOSE(SUM(--(last3="Red"))+1,"Green","Green","Amber","Red"))
A4:A17	A4	=EDATE(A3,1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1	Expression	=LET(last3,TAKE(FILTER(B3:B17,(B3:B17<>""),""),-3), CHOOSE(SUM(--(last3="Red"))+1,"Green","Green","Amber","Red"))="Amber"	text	NO
C1	Expression	=LET(last3,TAKE(FILTER(B3:B17,(B3:B17<>""),""),-3), CHOOSE(SUM(--(last3="Red"))+1,"Green","Green","Amber","Red"))="Red"	text	NO
C1	Expression	=LET(last3,TAKE(FILTER(B3:B17,(B3:B17<>""),""),-3), CHOOSE(SUM(--(last3="Red"))+1,"Green","Green","Amber","Red"))="Green"	text	NO