IF Function for RAG

PaulyK

Board Regular
Joined
Aug 27, 2015
Messages
50
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. 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
Book1
ABCDEFGHIJ
2Project #Project NameProject TierOverall RAGRAT UpdatedApproved BudgetActual SpendDifferenceStart DateCompletion Date
3220003Project 1Tier 3No£ -£ -£0.0011/04/2231/05/22
4220002Project 2Tier 1No£ -£ -£0.0001/04/2228/10/22
5220001Project 3Tier 2Tier 3 Project£ -£ -£0.0001/04/2201/04/22
6210186Project 4Tier 2Yes£ -£ -£0.0031/03/2229/06/22
7210185Project 5Tier 2Yes£ 18,000.00£ 24,000.00-£6,000.0031/03/2230/05/22
8210184Project 6Tier 1Yes£ -£ -£0.0031/03/2230/05/22
9210183Project 7Tier 2Yes£ -£ -£0.0031/03/2231/05/22
10210182Project 8Tier 1No£ -£ -£0.0003/01/2229/06/22
11210181Project 9Tier 3No£ 12,000.00£ 14,000.00-£2,000.0021/03/2227/05/22
12210180Project 10Tier 3No£ 12,000.00£ 12,999.00-£999.0021/03/2208/04/22
13210179Project 11Tier 3No£ -£ -£0.0014/03/2208/04/22
14210178Project 12Tier 3No£ -£ -£0.0023/03/2201/04/22
15210177Project 13Tier 2Yes£ 49,800.00£ -£49,800.0025/02/2230/05/22
16210176Project 14Tier 3Tier 3 Project£ -£ -£0.0001/02/2228/02/22
17210175Project 15Tier 1Yes£ 511,865.00£ -£511,865.0025/02/2231/08/22
18210174Project 16Tier 2Yes£ -£ -£0.0003/03/2230/05/22
19210173Project 17Tier 3Tier 3 Project£ -£ -£0.0021/02/2229/04/22
20210172Project 18Tier 1Yes£ 100,000.00£ -£100,000.0003/02/2230/05/22
21210171Project 19Tier 2No£ 40,000.00£ -£40,000.0001/03/2230/05/22
22210170Project 20Tier 2Yes£ -£ -£0.0001/03/2230/05/22
23210169Project 21Tier 1Yes£ -£ 1,852.00-£1,852.0001/07/2128/04/22
24210168Project 22Tier 1Yes£ -£ -£0.0001/03/2228/04/22
25210167Project 23Tier 2Yes£ 37,500.00£ 713.00£36,787.0018/02/2228/04/22
26210166Project 24Tier 1Yes£ 36,000.00£ 1,125.00£34,875.0017/03/2225/05/22
27210165Project 25Tier 2No£ -£ -£0.0014/02/2214/04/22
28210164Project 26Tier 1No£ 145,000.00£ 5,143.00£139,857.0031/01/2230/03/22
29210163Project 27Tier 1Yes£ 288,000.00£ 82,537.00£205,463.0031/01/2228/04/22
30210162Project 28Tier 2No£ -£ 1,029.00-£1,029.0021/02/2230/03/22
31210161Project 29Tier 2Yes£ 100,000.00£ 2,654.00£97,346.0001/11/2130/03/22
32210160Project 30Tier 2No£ -£ 7,287.00-£7,287.0008/12/2125/03/22
33210159Project 31Tier 3Tier 3 Project£ -£ -£0.0002/02/2215/04/22
34210158Project 32Tier 2No£ 21,600.00£ 16,360.00£5,240.0028/01/2231/03/22
35210157Project 33Tier 2Yes£ 57,000.00£ 48,266.00£8,734.0024/01/2224/03/22
36210156Project 34Tier 2Yes£ 55,100.00£ 31,140.00£23,960.0006/10/2125/03/22
37210155Project 35Tier 3Tier 3 Project£ -£ 2,477.00-£2,477.0007/02/2225/02/22
38210154Project 36Tier 1No£ 160,300.00£ 160,000.00£300.0019/01/2221/02/22
39210153Project 37Tier 2Yes£ 15,250.00£ 77.00£15,173.0007/02/2202/05/22
40210152Project 38Tier 1No£ 170,600.00£ 9,242.00£161,358.0013/01/2230/05/22
41210151Project 39Tier 1No£ 130,000.00£ 14,382.00£115,618.0016/12/2130/03/22
42210150Project 40Tier 1Tier 3 Project£ 1,319,565.00£ 214,111.00£1,105,454.0006/12/2128/07/22
Project Status Summary
Cell Formulas
RangeFormula
F3:F42F3=VLOOKUP(TEXT($A3,"######"),TblPPMEx,4,FALSE)
G3:G42G3=IFERROR(VLOOKUP(TEXT($A3,"######"),TblPPMEx,5,FALSE),"")
H3:H42H3=[@[Approved Budget]]-[@[Actual Spend]]
I3:I42I3=IFERROR(VLOOKUP(TEXT($A3,"######"),TblPPMEx,6,FALSE),"")
J3:J42J3=IFERROR(VLOOKUP(TEXT($A3,"######"),'PPM Extract'!A2:H41,7,FALSE),"")
A3:A42A3=INDEX(TblPPMEx[Project number],MATCH([@[Project Name]],TblPPMEx[Project name],0))
C3:C42C3=IFERROR(VLOOKUP(TEXT($A3,"######"),TblPPMEx,3,FALSE),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H3:H42Cell Value<-1000textNO
G3:G42Expression=$G3>$F3textNO
G3:G42Expression=$G3<$F3textNO
D4:D42Expression=$D4="Red"textNO
D3Expression=$D3="Amber"textNO
D3Expression=$D3="Green"textNO
D3Expression=$D3="Red"textNO
E3:E42Expression=$E3="Tier 3 Project"textNO
E3:E42Expression=$E3="No"textNO
E3:E42Expression=$E3="Yes"textNO
D4:D42Expression=$D4="Green"textNO
D4:D42Expression=$D4="Amber"textNO
J3:J42Expression=AND($J3>TODAY(), $J3-TODAY()<=30)textNO
J3:J42Expression=$J3<TODAY()textNO
J3:J42Expression=$J3=TODAY()+30textNO
Cells with Data Validation
CellAllowCriteria
D3:D42List=rngRAG


RAG SCORE
Book1
ABCDEF
1IDProjectRAGBudgetDateScore
2220003Project 12114
3220002Project 22114
4220001Project 32125
5210186Project 41113
6210185Project 51214
7210184Project 61113
8210183Project 71113
9210182Project 82114
10210181Project 92215
11210180Project 102125
12210179Project 112125
13210178Project 122125
14210177Project 131113
15210176Project 142125
16210175Project 151113
17210174Project 161113
18210173Project 172114
19210172Project 181113
20210171Project 192114
21210170Project 201113
22210169Project 211214
23210168Project 221113
24210167Project 231113
25210166Project 241113
26210165Project 252125
27210164Project 262125
28210163Project 271113
29210162Project 282226
30210161Project 291124
31210160Project 302226
32210159Project 312125
33210158Project 322125
34210157Project 331124
35210156Project 341124
36210155Project 352226
37210154Project 362125
38210153Project 371113
39210152Project 382114
40210151Project 392125
41210150Project 402114
Score
Cell Formulas
RangeFormula
B2:B41B2=UNIQUE(Table1[Project Name])
C2:C41C2=IF('Project Status Summary'!E3="Yes", "1", "2")
D2:D41D2=IF('Project Status Summary'!H3<-1000,"2","1")
E2:E41E2=IF('Project Status Summary'!J3<=TODAY(),"2","1")
F2:F41F2=C2+D2+E2
A2:A41A2=INDEX('PPM Extract'!$A2:$A$738,MATCH(Score!B2,'PPM Extract'!B2:B738,0))
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F2:F656Expression=$F2=3textNO
F2:F656Expression=$F2=4textNO
F2:F656Expression=$F2=5textNO
F2:F656Expression=$F2=6textNO


Data Extract
Book1
ABCDEFGH
1Project numberProject nameTypeApproved budgetActual spendPlanned start dateForecast end dateActual end date
2220003Project 1Tier 3£0.00£0.0011/04/2022 08:00:0031/05/2022 17:00:00
3220002Project 2Tier 1£0.00£0.0001/04/2022 07:00:0028/10/2022 17:00:00
4220001Project 3Tier 2£0.00£0.0001/04/2022 07:00:0001/04/2022 07:00:0001/04/2022 07:00:00
5210186Project 4Tier 2£0.00£0.0031/03/2022 07:00:0029/06/2022 17:00:00
6210185Project 5Tier 2£18,000.00£24,000.0031/03/2022 07:00:0030/05/2022 17:00:00
7210184Project 6Tier 1£0.00£0.0031/03/2022 07:00:0030/05/2022 17:00:00
8210183Project 7Tier 2£0.00£0.0031/03/2022 07:00:0031/05/2022 17:00:00
9210182Project 8Tier 1£0.00£0.0003/01/2022 08:00:0029/06/2022 17:00:00
10210181Project 9Tier 3£12,000.00£14,000.0021/03/2022 08:00:0027/05/2022 17:00:00
11210180Project 10Tier 3£12,000.00£12,999.0021/03/2022 08:00:0008/04/2022 17:00:00
12210179Project 11Tier 3£0.00£0.0014/03/2022 08:00:0008/04/2022 17:00:00
13210178Project 12Tier 3£0.00£0.0023/03/2022 08:00:0001/04/2022 17:00:00
14210177Project 13Tier 2£49,800.00£0.0025/02/2022 08:00:0030/05/2022 17:00:00
15210176Project 14Tier 3£0.00£0.0001/02/2022 08:00:0028/02/2022 17:00:00
16210175Project 15Tier 1£511,865.00£0.0025/02/2022 08:00:0031/08/2022 17:00:00
17210174Project 16Tier 2£0.00£0.0003/03/2022 08:00:0030/05/2022 17:00:00
18210173Project 17Tier 3£0.00£0.0021/02/2022 08:00:0029/04/2022 17:00:0029/04/2022 17:00:00
19210172Project 18Tier 1£100,000.00£0.0003/02/2022 08:00:0030/05/2022 17:00:00
20210171Project 19Tier 2£40,000.00£0.0001/03/2022 08:00:0030/05/2022 17:00:00
21210170Project 20Tier 2£0.00£0.0001/03/2022 08:00:0030/05/2022 17:00:00
22210169Project 21Tier 1£0.00£1,852.0001/07/2021 07:00:0028/04/2022 17:00:00
23210168Project 22Tier 1£0.00£0.0001/03/2022 08:00:0028/04/2022 17:00:00
24210167Project 23Tier 2£37,500.00£713.0018/02/2022 08:00:0028/04/2022 17:00:00
25210166Project 24Tier 1£36,000.00£1,125.0017/03/2022 08:00:0025/05/2022 17:00:00
26210165Project 25Tier 2£0.00£0.0014/02/2022 08:00:0014/04/2022 17:00:00
27210164Project 26Tier 1£145,000.00£5,143.0031/01/2022 08:00:0030/03/2022 17:00:00
28210163Project 27Tier 1£288,000.00£82,537.0031/01/2022 08:00:0028/04/2022 17:00:00
29210162Project 28Tier 2£0.00£1,029.0021/02/2022 08:00:0030/03/2022 17:00:00
30210161Project 29Tier 2£100,000.00£2,654.0001/11/2021 08:00:0030/03/2022 17:00:00
31210160Project 30Tier 2£0.00£7,287.0008/12/2021 08:00:0025/03/2022 17:00:00
32210159Project 31Tier 3£0.00£0.0002/02/2022 08:00:0015/04/2022 17:00:00
33210158Project 32Tier 2£21,600.00£16,360.0028/01/2022 08:00:0031/03/2022 17:00:0031/03/2022 17:00:00
34210157Project 33Tier 2£57,000.00£48,266.0024/01/2022 08:00:0024/03/2022 17:00:00
35210156Project 34Tier 2£55,100.00£31,140.0006/10/2021 07:00:0025/03/2022 17:00:0025/03/2022 17:00:00
36210155Project 35Tier 3£0.00£2,477.0007/02/2022 08:00:0025/02/2022 17:00:00
37210154Project 36Tier 1£160,300.00£160,000.0019/01/2022 08:00:0021/02/2022 08:57:39
38210153Project 37Tier 2£15,250.00£77.0007/02/2022 08:00:0002/05/2022 15:32:13
39210152Project 38Tier 1£170,600.00£9,242.0013/01/2022 08:00:0030/05/2022 17:00:00
40210151Project 39Tier 1£130,000.00£14,382.0016/12/2021 08:00:0030/03/2022 17:00:00
41210150Project 40Tier 1£1,319,565.00£214,111.0006/12/2021 08:00:0028/07/2022 17:00:00
PPM Extract
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Forum statistics

Threads
1,223,214
Messages
6,170,774
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top