PaulyK
Board Regular
- Joined
- Aug 27, 2015
- Messages
- 50
- Office Version
- 365
- Platform
- Windows
- MacOS
- Mobile
- Web
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?)
RAG Scoring
Summary
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
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 |