IF Function for RAG

[PaulyK]

Hi,

I have created a Project Status Summary and want to use an IF (AND/OR) type statement to automatically calculate the score.

In Column D I want to display RAG rating of the following RED, AMBER, GREEN or No RAG
SCORE
= 3 (ALL Criteria Matched) =GREEN, 4 Points = GREEN, 5 Points = AMBER, 6 Points (No Criteria Matched) = RED
There is an exception for Tier 3 Projects that they are not scored and should display "No RAG"

Using (a very crude!) Score Table I have scored each Project out of 6 based on the data in Columns E (Update), H (Spend Difference) and I (Completion Date)
Column E is updated from a separate Data source. Column H is the difference between Approved Budget and Actual Spend with the criteria if the difference is greater than Minus £1000 as a negative result (2 points on the score) and Column I calculates the completion date from Today and if the date has passed, then it is scored 2.

I also need to work out how to exclude any Project that is Tier 3 (as shown in in Column E and also Column)

I attempted with an IF / OR Statement but it didn't work and i could only display RED, with the other cells being TRUE / FALSE. I couldn't see how to add the 'Tier 3' exclusin either

Any tips?

Thanks

Project Status Summary

Book1
	A	B	C	D	E	F	G	H	I	J
2	Project #	Project Name	Project Tier	Overall RAG	RAT Updated	Approved Budget	Actual Spend	Difference	Start Date	Completion Date
3	220003	Project 1	Tier 3		No	£ -	£ -	£0.00	11/04/22	31/05/22
4	220002	Project 2	Tier 1		No	£ -	£ -	£0.00	01/04/22	28/10/22
5	220001	Project 3	Tier 2		Tier 3 Project	£ -	£ -	£0.00	01/04/22	01/04/22
6	210186	Project 4	Tier 2		Yes	£ -	£ -	£0.00	31/03/22	29/06/22
7	210185	Project 5	Tier 2		Yes	£ 18,000.00	£ 24,000.00	-£6,000.00	31/03/22	30/05/22
8	210184	Project 6	Tier 1		Yes	£ -	£ -	£0.00	31/03/22	30/05/22
9	210183	Project 7	Tier 2		Yes	£ -	£ -	£0.00	31/03/22	31/05/22
10	210182	Project 8	Tier 1		No	£ -	£ -	£0.00	03/01/22	29/06/22
11	210181	Project 9	Tier 3		No	£ 12,000.00	£ 14,000.00	-£2,000.00	21/03/22	27/05/22
12	210180	Project 10	Tier 3		No	£ 12,000.00	£ 12,999.00	-£999.00	21/03/22	08/04/22
13	210179	Project 11	Tier 3		No	£ -	£ -	£0.00	14/03/22	08/04/22
14	210178	Project 12	Tier 3		No	£ -	£ -	£0.00	23/03/22	01/04/22
15	210177	Project 13	Tier 2		Yes	£ 49,800.00	£ -	£49,800.00	25/02/22	30/05/22
16	210176	Project 14	Tier 3		Tier 3 Project	£ -	£ -	£0.00	01/02/22	28/02/22
17	210175	Project 15	Tier 1		Yes	£ 511,865.00	£ -	£511,865.00	25/02/22	31/08/22
18	210174	Project 16	Tier 2		Yes	£ -	£ -	£0.00	03/03/22	30/05/22
19	210173	Project 17	Tier 3		Tier 3 Project	£ -	£ -	£0.00	21/02/22	29/04/22
20	210172	Project 18	Tier 1		Yes	£ 100,000.00	£ -	£100,000.00	03/02/22	30/05/22
21	210171	Project 19	Tier 2		No	£ 40,000.00	£ -	£40,000.00	01/03/22	30/05/22
22	210170	Project 20	Tier 2		Yes	£ -	£ -	£0.00	01/03/22	30/05/22
23	210169	Project 21	Tier 1		Yes	£ -	£ 1,852.00	-£1,852.00	01/07/21	28/04/22
24	210168	Project 22	Tier 1		Yes	£ -	£ -	£0.00	01/03/22	28/04/22
25	210167	Project 23	Tier 2		Yes	£ 37,500.00	£ 713.00	£36,787.00	18/02/22	28/04/22
26	210166	Project 24	Tier 1		Yes	£ 36,000.00	£ 1,125.00	£34,875.00	17/03/22	25/05/22
27	210165	Project 25	Tier 2		No	£ -	£ -	£0.00	14/02/22	14/04/22
28	210164	Project 26	Tier 1		No	£ 145,000.00	£ 5,143.00	£139,857.00	31/01/22	30/03/22
29	210163	Project 27	Tier 1		Yes	£ 288,000.00	£ 82,537.00	£205,463.00	31/01/22	28/04/22
30	210162	Project 28	Tier 2		No	£ -	£ 1,029.00	-£1,029.00	21/02/22	30/03/22
31	210161	Project 29	Tier 2		Yes	£ 100,000.00	£ 2,654.00	£97,346.00	01/11/21	30/03/22
32	210160	Project 30	Tier 2		No	£ -	£ 7,287.00	-£7,287.00	08/12/21	25/03/22
33	210159	Project 31	Tier 3		Tier 3 Project	£ -	£ -	£0.00	02/02/22	15/04/22
34	210158	Project 32	Tier 2		No	£ 21,600.00	£ 16,360.00	£5,240.00	28/01/22	31/03/22
35	210157	Project 33	Tier 2		Yes	£ 57,000.00	£ 48,266.00	£8,734.00	24/01/22	24/03/22
36	210156	Project 34	Tier 2		Yes	£ 55,100.00	£ 31,140.00	£23,960.00	06/10/21	25/03/22
37	210155	Project 35	Tier 3		Tier 3 Project	£ -	£ 2,477.00	-£2,477.00	07/02/22	25/02/22
38	210154	Project 36	Tier 1		No	£ 160,300.00	£ 160,000.00	£300.00	19/01/22	21/02/22
39	210153	Project 37	Tier 2		Yes	£ 15,250.00	£ 77.00	£15,173.00	07/02/22	02/05/22
40	210152	Project 38	Tier 1		No	£ 170,600.00	£ 9,242.00	£161,358.00	13/01/22	30/05/22
41	210151	Project 39	Tier 1		No	£ 130,000.00	£ 14,382.00	£115,618.00	16/12/21	30/03/22
42	210150	Project 40	Tier 1		Tier 3 Project	£ 1,319,565.00	£ 214,111.00	£1,105,454.00	06/12/21	28/07/22
Project Status Summary

Cell Formulas
Range	Formula
F3:F42	F3	=VLOOKUP(TEXT($A3,"######"),TblPPMEx,4,FALSE)
G3:G42	G3	=IFERROR(VLOOKUP(TEXT($A3,"######"),TblPPMEx,5,FALSE),"")
H3:H42	H3	=[@[Approved Budget]]-[@[Actual Spend]]
I3:I42	I3	=IFERROR(VLOOKUP(TEXT($A3,"######"),TblPPMEx,6,FALSE),"")
J3:J42	J3	=IFERROR(VLOOKUP(TEXT($A3,"######"),'PPM Extract'!A2:H41,7,FALSE),"")
A3:A42	A3	=INDEX(TblPPMEx[Project number],MATCH([@[Project Name]],TblPPMEx[Project name],0))
C3:C42	C3	=IFERROR(VLOOKUP(TEXT($A3,"######"),TblPPMEx,3,FALSE),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H3:H42	Cell Value	<-1000	text	NO
G3:G42	Expression	=$G3>$F3	text	NO
G3:G42	Expression	=$G3<$F3	text	NO
D4:D42	Expression	=$D4="Red"	text	NO
D3	Expression	=$D3="Amber"	text	NO
D3	Expression	=$D3="Green"	text	NO
D3	Expression	=$D3="Red"	text	NO
E3:E42	Expression	=$E3="Tier 3 Project"	text	NO
E3:E42	Expression	=$E3="No"	text	NO
E3:E42	Expression	=$E3="Yes"	text	NO
D4:D42	Expression	=$D4="Green"	text	NO
D4:D42	Expression	=$D4="Amber"	text	NO
J3:J42	Expression	=AND($J3>TODAY(), $J3-TODAY()<=30)	text	NO
J3:J42	Expression	=$J3<TODAY()	text	NO
J3:J42	Expression	=$J3=TODAY()+30	text	NO

Cells with Data Validation
Cell	Allow	Criteria
D3:D42	List	=rngRAG

RAG SCORE

Book1
	A	B	C	D	E	F
1	ID	Project	RAG	Budget	Date	Score
2	220003	Project 1	2	1	1	4
3	220002	Project 2	2	1	1	4
4	220001	Project 3	2	1	2	5
5	210186	Project 4	1	1	1	3
6	210185	Project 5	1	2	1	4
7	210184	Project 6	1	1	1	3
8	210183	Project 7	1	1	1	3
9	210182	Project 8	2	1	1	4
10	210181	Project 9	2	2	1	5
11	210180	Project 10	2	1	2	5
12	210179	Project 11	2	1	2	5
13	210178	Project 12	2	1	2	5
14	210177	Project 13	1	1	1	3
15	210176	Project 14	2	1	2	5
16	210175	Project 15	1	1	1	3
17	210174	Project 16	1	1	1	3
18	210173	Project 17	2	1	1	4
19	210172	Project 18	1	1	1	3
20	210171	Project 19	2	1	1	4
21	210170	Project 20	1	1	1	3
22	210169	Project 21	1	2	1	4
23	210168	Project 22	1	1	1	3
24	210167	Project 23	1	1	1	3
25	210166	Project 24	1	1	1	3
26	210165	Project 25	2	1	2	5
27	210164	Project 26	2	1	2	5
28	210163	Project 27	1	1	1	3
29	210162	Project 28	2	2	2	6
30	210161	Project 29	1	1	2	4
31	210160	Project 30	2	2	2	6
32	210159	Project 31	2	1	2	5
33	210158	Project 32	2	1	2	5
34	210157	Project 33	1	1	2	4
35	210156	Project 34	1	1	2	4
36	210155	Project 35	2	2	2	6
37	210154	Project 36	2	1	2	5
38	210153	Project 37	1	1	1	3
39	210152	Project 38	2	1	1	4
40	210151	Project 39	2	1	2	5
41	210150	Project 40	2	1	1	4
Score

Cell Formulas
Range	Formula
B2:B41	B2	=UNIQUE(Table1[Project Name])
C2:C41	C2	=IF('Project Status Summary'!E3="Yes", "1", "2")
D2:D41	D2	=IF('Project Status Summary'!H3<-1000,"2","1")
E2:E41	E2	=IF('Project Status Summary'!J3<=TODAY(),"2","1")
F2:F41	F2	=C2+D2+E2
A2:A41	A2	=INDEX('PPM Extract'!$A2:$A$738,MATCH(Score!B2,'PPM Extract'!B2:B738,0))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:F656	Expression	=$F2=3	text	NO
F2:F656	Expression	=$F2=4	text	NO
F2:F656	Expression	=$F2=5	text	NO
F2:F656	Expression	=$F2=6	text	NO

Data Extract

Book1
	A	B	C	D	E	F	G	H
1	Project number	Project name	Type	Approved budget	Actual spend	Planned start date	Forecast end date	Actual end date
2	220003	Project 1	Tier 3	£0.00	£0.00	11/04/2022 08:00:00	31/05/2022 17:00:00
3	220002	Project 2	Tier 1	£0.00	£0.00	01/04/2022 07:00:00	28/10/2022 17:00:00
4	220001	Project 3	Tier 2	£0.00	£0.00	01/04/2022 07:00:00	01/04/2022 07:00:00	01/04/2022 07:00:00
5	210186	Project 4	Tier 2	£0.00	£0.00	31/03/2022 07:00:00	29/06/2022 17:00:00
6	210185	Project 5	Tier 2	£18,000.00	£24,000.00	31/03/2022 07:00:00	30/05/2022 17:00:00
7	210184	Project 6	Tier 1	£0.00	£0.00	31/03/2022 07:00:00	30/05/2022 17:00:00
8	210183	Project 7	Tier 2	£0.00	£0.00	31/03/2022 07:00:00	31/05/2022 17:00:00
9	210182	Project 8	Tier 1	£0.00	£0.00	03/01/2022 08:00:00	29/06/2022 17:00:00
10	210181	Project 9	Tier 3	£12,000.00	£14,000.00	21/03/2022 08:00:00	27/05/2022 17:00:00
11	210180	Project 10	Tier 3	£12,000.00	£12,999.00	21/03/2022 08:00:00	08/04/2022 17:00:00
12	210179	Project 11	Tier 3	£0.00	£0.00	14/03/2022 08:00:00	08/04/2022 17:00:00
13	210178	Project 12	Tier 3	£0.00	£0.00	23/03/2022 08:00:00	01/04/2022 17:00:00
14	210177	Project 13	Tier 2	£49,800.00	£0.00	25/02/2022 08:00:00	30/05/2022 17:00:00
15	210176	Project 14	Tier 3	£0.00	£0.00	01/02/2022 08:00:00	28/02/2022 17:00:00
16	210175	Project 15	Tier 1	£511,865.00	£0.00	25/02/2022 08:00:00	31/08/2022 17:00:00
17	210174	Project 16	Tier 2	£0.00	£0.00	03/03/2022 08:00:00	30/05/2022 17:00:00
18	210173	Project 17	Tier 3	£0.00	£0.00	21/02/2022 08:00:00	29/04/2022 17:00:00	29/04/2022 17:00:00
19	210172	Project 18	Tier 1	£100,000.00	£0.00	03/02/2022 08:00:00	30/05/2022 17:00:00
20	210171	Project 19	Tier 2	£40,000.00	£0.00	01/03/2022 08:00:00	30/05/2022 17:00:00
21	210170	Project 20	Tier 2	£0.00	£0.00	01/03/2022 08:00:00	30/05/2022 17:00:00
22	210169	Project 21	Tier 1	£0.00	£1,852.00	01/07/2021 07:00:00	28/04/2022 17:00:00
23	210168	Project 22	Tier 1	£0.00	£0.00	01/03/2022 08:00:00	28/04/2022 17:00:00
24	210167	Project 23	Tier 2	£37,500.00	£713.00	18/02/2022 08:00:00	28/04/2022 17:00:00
25	210166	Project 24	Tier 1	£36,000.00	£1,125.00	17/03/2022 08:00:00	25/05/2022 17:00:00
26	210165	Project 25	Tier 2	£0.00	£0.00	14/02/2022 08:00:00	14/04/2022 17:00:00
27	210164	Project 26	Tier 1	£145,000.00	£5,143.00	31/01/2022 08:00:00	30/03/2022 17:00:00
28	210163	Project 27	Tier 1	£288,000.00	£82,537.00	31/01/2022 08:00:00	28/04/2022 17:00:00
29	210162	Project 28	Tier 2	£0.00	£1,029.00	21/02/2022 08:00:00	30/03/2022 17:00:00
30	210161	Project 29	Tier 2	£100,000.00	£2,654.00	01/11/2021 08:00:00	30/03/2022 17:00:00
31	210160	Project 30	Tier 2	£0.00	£7,287.00	08/12/2021 08:00:00	25/03/2022 17:00:00
32	210159	Project 31	Tier 3	£0.00	£0.00	02/02/2022 08:00:00	15/04/2022 17:00:00
33	210158	Project 32	Tier 2	£21,600.00	£16,360.00	28/01/2022 08:00:00	31/03/2022 17:00:00	31/03/2022 17:00:00
34	210157	Project 33	Tier 2	£57,000.00	£48,266.00	24/01/2022 08:00:00	24/03/2022 17:00:00
35	210156	Project 34	Tier 2	£55,100.00	£31,140.00	06/10/2021 07:00:00	25/03/2022 17:00:00	25/03/2022 17:00:00
36	210155	Project 35	Tier 3	£0.00	£2,477.00	07/02/2022 08:00:00	25/02/2022 17:00:00
37	210154	Project 36	Tier 1	£160,300.00	£160,000.00	19/01/2022 08:00:00	21/02/2022 08:57:39
38	210153	Project 37	Tier 2	£15,250.00	£77.00	07/02/2022 08:00:00	02/05/2022 15:32:13
39	210152	Project 38	Tier 1	£170,600.00	£9,242.00	13/01/2022 08:00:00	30/05/2022 17:00:00
40	210151	Project 39	Tier 1	£130,000.00	£14,382.00	16/12/2021 08:00:00	30/03/2022 17:00:00
41	210150	Project 40	Tier 1	£1,319,565.00	£214,111.00	06/12/2021 08:00:00	28/07/2022 17:00:00
PPM Extract

 

[Fluff]

How about

=IF(C3="Tier 3","No Rag",SWITCH(IFNA(VLOOKUP(A3,score!A2:F100,6,1),""),3,"Green",4,"Green",5,"Amber",6,"Red",""))

 

[PaulyK]

Oh that is brilliant. Thank you (again!)

Would you be able to guide me through the steps? I think i understand it, but haven't used (or seen) SWITCH before

Thanks

 

[Fluff]

The switch function simply switches one value for another. How to use the Excel SWITCH function | Exceljet

 

[PaulyK]

How about

=IF(C3="Tier 3","No Rag",SWITCH(IFNA(VLOOKUP(A3,score!A2:F100,6,1),""),3,"Green",4,"Green",5,"Amber",6,"Red",""))

The switch function simply switches one value for another. How to use the Excel SWITCH function | Exceljet

ah got it. Another one to add to the list!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[PaulyK]

How about

=IF(C3="Tier 3","No Rag",SWITCH(IFNA(VLOOKUP(A3,score!A2:F100,6,1),""),3,"Green",4,"Green",5,"Amber",6,"Red",""))

IF i wanted to reference the cells rather than having '3', '4', '5', '6' in the formula, is that do able? (actually i know it is) - I am thinking of ways so the Users of the form would not have to change the formula if they decided on a change of RAG rating. I am guessing you would not be able to do the same with the text though of "Green" "Amber" etc?

 

[Fluff]

I'm afraid I don't understand what your asking.

 

[PaulyK]

Sorry. If I had a table with 1 column the number 3-6 and a second column with The RAG status Green, Amber, Red would If be possible to reference them in the formula rather than typing 2,3 or “Red” etc?

It was just a thought but actually probably too complex (and potentially unworkable) thinking about it

 

[Fluff]

That's possible, but a better option would be to setup the other table & then on the score sheet in col G use a lookup to populate the RAG status & then use the Vlookup to pull from col G rather than col F