PaulyK
Board Regular
- Joined
- Aug 27, 2015
- Messages
- 50
- Office Version
- 365
- Platform
- Windows
- MacOS
- Mobile
- Web
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
RAG SCORE
Data Extract
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
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 |