IF with multiple criteria

[PaulyK]

I have a RAG scoring system which gives a result of 3, 4, 5 or 6 based on Results in 3 separate columns (RAT, Budget, Date)
RAT = Shows if Resource Tracker has been updated Yes-1 or No-2
Budget = Shows If the difference between Budget and Spend is negative and more than £1000 Yes-2, No-1
Date = Shows if the Completion day has passed. Yes-2, No-1


I currently have the formula below in Column D
=IF('Project Status Summary'!H3<-1000,"2","1")
but am looking to expand this using the below criteria

I attempted =IF('Project Status Summary'!H3>=0,1,IF('Project Status Summary'!H3<=-1000,1,IF('Project Status Summary'!H3>=-1001,3,4))) but I am clearly missing something (AND / OR maybe?)

If Variance is 0 or More than 0 then = 1

If Variance is below 0 but <-1000 = `1

If Variance is >1000 but less than -2500 = 2

If Variance is Greater than -2500 =3

RAG Scoring

RAG Attempt.xlsm
	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	2	5
23	210168	Project 22	1	1	2	4
24	210167	Project 23	1	1	2	4
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	2	4
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

Summary

RAG Attempt.xlsm
	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 Rag	No	£ -	£ -	£0.00	11/04/22	31/05/22
4	220002	Project 2	Tier 1	Green	No	£ -	£ -	£0.00	01/04/22	28/10/22
5	220001	Project 3	Tier 2	Green	Tier 3 Project	£ -	£ -	£0.00	01/04/22	01/04/22
6	210186	Project 4	Tier 2	Green	Yes	£ -	£ -	£0.00	31/03/22	29/06/22
7	210185	Project 5	Tier 2	Green	Yes	£ 18,000.00	£ 24,000.00	-£6,000.00	31/03/22	30/05/22
8	210184	Project 6	Tier 1	Green	Yes	£ -	£ -	£0.00	31/03/22	30/05/22
9	210183	Project 7	Tier 2	Green	Yes	£ -	£ -	£0.00	31/03/22	31/05/22
10	210182	Project 8	Tier 1	Green	No	£ -	£ -	£0.00	03/01/22	29/06/22
11	210181	Project 9	Tier 3	No Rag	No	£ 12,000.00	£ 14,000.00	-£2,000.00	21/03/22	27/05/22
12	210180	Project 10	Tier 3	No Rag	No	£ 12,000.00	£ 12,999.00	-£999.00	21/03/22	08/04/22
13	210179	Project 11	Tier 3	No Rag	No	£ -	£ -	£0.00	14/03/22	08/04/22
14	210178	Project 12	Tier 3	No Rag	No	£ -	£ -	£0.00	23/03/22	01/04/22
15	210177	Project 13	Tier 2	Green	Yes	£ 49,800.00	£ -	£49,800.00	25/02/22	30/05/22
16	210176	Project 14	Tier 3	No Rag	Tier 3 Project	£ -	£ -	£0.00	01/02/22	28/02/22
17	210175	Project 15	Tier 1	Green	Yes	£ 511,865.00	£ -	£511,865.00	25/02/22	31/08/22
18	210174	Project 16	Tier 2	Green	Yes	£ -	£ -	£0.00	03/03/22	30/05/22
19	210173	Project 17	Tier 3	No Rag	Tier 3 Project	£ -	£ -	£0.00	21/02/22	29/04/22
20	210172	Project 18	Tier 1	Green	Yes	£ 100,000.00	£ -	£100,000.00	03/02/22	30/05/22
21	210171	Project 19	Tier 2	Green	No	£ 40,000.00	£ -	£40,000.00	01/03/22	30/05/22
22	210170	Project 20	Tier 2	Green	Yes	£ -	£ -	£0.00	01/03/22	30/05/22
23	210169	Project 21	Tier 1	Green	Yes	£ -	£ 1,852.00	-£1,852.00	01/07/21	28/04/22
24	210168	Project 22	Tier 1	Green	Yes	£ -	£ -	£0.00	01/03/22	28/04/22
25	210167	Project 23	Tier 2	Green	Yes	£ 37,500.00	£ 713.00	£36,787.00	18/02/22	28/04/22
26	210166	Project 24	Tier 1	Green	Yes	£ 36,000.00	£ 1,125.00	£34,875.00	17/03/22	25/05/22
27	210165	Project 25	Tier 2	Green	No	£ -	£ -	£0.00	14/02/22	14/04/22
28	210164	Project 26	Tier 1	Green	No	£ 145,000.00	£ 5,143.00	£139,857.00	31/01/22	30/03/22
29	210163	Project 27	Tier 1	Green	Yes	£ 288,000.00	£ 82,537.00	£205,463.00	31/01/22	28/04/22
30	210162	Project 28	Tier 2	Green	No	£ -	£ 1,029.00	-£1,029.00	21/02/22	30/03/22
31	210161	Project 29	Tier 2	Green	Yes	£ 100,000.00	£ 2,654.00	£97,346.00	01/11/21	30/03/22
32	210160	Project 30	Tier 2	Green	No	£ -	£ 7,287.00	-£7,287.00	08/12/21	25/03/22
33	210159	Project 31	Tier 3	No Rag	Tier 3 Project	£ -	£ -	£0.00	02/02/22	15/04/22
34	210158	Project 32	Tier 2	Green	No	£ 21,600.00	£ 16,360.00	£5,240.00	28/01/22	31/03/22
35	210157	Project 33	Tier 2	Green	Yes	£ 57,000.00	£ 48,266.00	£8,734.00	24/01/22	24/03/22
36	210156	Project 34	Tier 2	Green	Yes	£ 55,100.00	£ 31,140.00	£23,960.00	06/10/21	25/03/22
37	210155	Project 35	Tier 3	No Rag	Tier 3 Project	£ -	£ 2,477.00	-£2,477.00	07/02/22	25/02/22
38	210154	Project 36	Tier 1	Green	No	£ 160,300.00	£ 160,000.00	£300.00	19/01/22	21/02/22
39	210153	Project 37	Tier 2	Green	Yes	£ 15,250.00	£ 77.00	£15,173.00	07/02/22	02/05/22
40	210152	Project 38	Tier 1	Green	No	£ 170,600.00	£ 9,242.00	£161,358.00	13/01/22	30/05/22
41	210151	Project 39	Tier 1	Amber	No	£ 130,000.00	£ 14,382.00	£115,618.00	16/12/21	30/03/22
42	210150	Project 40	Tier 1	Green	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
C3:C42	C3	=IFERROR(VLOOKUP(TEXT($A3,"######"),TblPPMEx,3,FALSE),"")
D3:D42	D3	=IF(C3="Tier 3","No Rag",SWITCH(IFNA(VLOOKUP(A3,Score!A2:$F$101,6,1),""),3,"Green",4,"Green",5,"Amber",6,"Red",""))
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))

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

 

[Joe4]

I currently have the formula below in Column D
=IF('Project Status Summary'!H3<-1000,"2","1")
but am looking to expand this using the below criteria

I attempted =IF('Project Status Summary'!H3>=0,1,IF('Project Status Summary'!H3<=-1000,1,IF('Project Status Summary'!H3>=-1001,3,4))) but I am clearly missing something (AND / OR maybe?)

If Variance is 0 or More than 0 then = 1

If Variance is below 0 but <-1000 = `1

If Variance is >1000 but less than -2500 = 2

If Variance is Greater than -2500 =3

Your explanation is very confusing. I think you have some signs and things backwards (note that -3000 is actually LESS THAN -2500, mathematically speaking).
Why don't we cover all the base?
Tell us what you want to return in each instance below:

Variance is less than -2500:
Variance is between -2500 and -1000:
Variance is between -1000 and 0:
Variance is between 0 and 1000:
Variance is between 1000 and 2500:
Variance is greater than 2500:

 

[PaulyK]

Your explanation is very confusing. I think you have some signs and things backwards (note that -3000 is actually LESS THAN -2500, mathematically speaking).
Why don't we cover all the base?
Tell us what you want to return in each instance below:

Variance is less than -2500:
Variance is between -2500 and -1000:
Variance is between -1000 and 0:
Variance is between 0 and 1000:
Variance is between 1000 and 2500:
Variance is greater than 2500:

Sorry late reply. Indeed it didn’t make much sense.
Variance is less than -2500: 3
Variance is between -2500 and -1000: 2
Variance is between -1000 and 0: 1
Variance is between 0 and 1000: 1
Variance is between 1000 and 2500:
Variance is greater than 2500:

 

[Joe4]

Maybe something like this:

=IF('Project Status Summary'!H3<-2500,3,IF('Project Status Summary'!H3<-1000,2,1))