IF with multiple criteria

PaulyK

Board Regular
Joined
Aug 27, 2015
Messages
50
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. 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?)

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
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 211225
23210168Project 221124
24210167Project 231124
25210166Project 241113
26210165Project 252125
27210164Project 262125
28210163Project 271124
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



Summary
RAG Attempt.xlsm
ABCDEFGHIJ
2Project #Project NameProject TierOverall RAGRAT UpdatedApproved BudgetActual SpendDifferenceStart DateCompletion Date
3220003Project 1Tier 3No RagNo£ -£ -£0.0011/04/2231/05/22
4220002Project 2Tier 1GreenNo£ -£ -£0.0001/04/2228/10/22
5220001Project 3Tier 2GreenTier 3 Project£ -£ -£0.0001/04/2201/04/22
6210186Project 4Tier 2GreenYes£ -£ -£0.0031/03/2229/06/22
7210185Project 5Tier 2GreenYes£ 18,000.00£ 24,000.00-£6,000.0031/03/2230/05/22
8210184Project 6Tier 1GreenYes£ -£ -£0.0031/03/2230/05/22
9210183Project 7Tier 2GreenYes£ -£ -£0.0031/03/2231/05/22
10210182Project 8Tier 1GreenNo£ -£ -£0.0003/01/2229/06/22
11210181Project 9Tier 3No RagNo£ 12,000.00£ 14,000.00-£2,000.0021/03/2227/05/22
12210180Project 10Tier 3No RagNo£ 12,000.00£ 12,999.00-£999.0021/03/2208/04/22
13210179Project 11Tier 3No RagNo£ -£ -£0.0014/03/2208/04/22
14210178Project 12Tier 3No RagNo£ -£ -£0.0023/03/2201/04/22
15210177Project 13Tier 2GreenYes£ 49,800.00£ -£49,800.0025/02/2230/05/22
16210176Project 14Tier 3No RagTier 3 Project£ -£ -£0.0001/02/2228/02/22
17210175Project 15Tier 1GreenYes£ 511,865.00£ -£511,865.0025/02/2231/08/22
18210174Project 16Tier 2GreenYes£ -£ -£0.0003/03/2230/05/22
19210173Project 17Tier 3No RagTier 3 Project£ -£ -£0.0021/02/2229/04/22
20210172Project 18Tier 1GreenYes£ 100,000.00£ -£100,000.0003/02/2230/05/22
21210171Project 19Tier 2GreenNo£ 40,000.00£ -£40,000.0001/03/2230/05/22
22210170Project 20Tier 2GreenYes£ -£ -£0.0001/03/2230/05/22
23210169Project 21Tier 1GreenYes£ -£ 1,852.00-£1,852.0001/07/2128/04/22
24210168Project 22Tier 1GreenYes£ -£ -£0.0001/03/2228/04/22
25210167Project 23Tier 2GreenYes£ 37,500.00£ 713.00£36,787.0018/02/2228/04/22
26210166Project 24Tier 1GreenYes£ 36,000.00£ 1,125.00£34,875.0017/03/2225/05/22
27210165Project 25Tier 2GreenNo£ -£ -£0.0014/02/2214/04/22
28210164Project 26Tier 1GreenNo£ 145,000.00£ 5,143.00£139,857.0031/01/2230/03/22
29210163Project 27Tier 1GreenYes£ 288,000.00£ 82,537.00£205,463.0031/01/2228/04/22
30210162Project 28Tier 2GreenNo£ -£ 1,029.00-£1,029.0021/02/2230/03/22
31210161Project 29Tier 2GreenYes£ 100,000.00£ 2,654.00£97,346.0001/11/2130/03/22
32210160Project 30Tier 2GreenNo£ -£ 7,287.00-£7,287.0008/12/2125/03/22
33210159Project 31Tier 3No RagTier 3 Project£ -£ -£0.0002/02/2215/04/22
34210158Project 32Tier 2GreenNo£ 21,600.00£ 16,360.00£5,240.0028/01/2231/03/22
35210157Project 33Tier 2GreenYes£ 57,000.00£ 48,266.00£8,734.0024/01/2224/03/22
36210156Project 34Tier 2GreenYes£ 55,100.00£ 31,140.00£23,960.0006/10/2125/03/22
37210155Project 35Tier 3No RagTier 3 Project£ -£ 2,477.00-£2,477.0007/02/2225/02/22
38210154Project 36Tier 1GreenNo£ 160,300.00£ 160,000.00£300.0019/01/2221/02/22
39210153Project 37Tier 2GreenYes£ 15,250.00£ 77.00£15,173.0007/02/2202/05/22
40210152Project 38Tier 1GreenNo£ 170,600.00£ 9,242.00£161,358.0013/01/2230/05/22
41210151Project 39Tier 1AmberNo£ 130,000.00£ 14,382.00£115,618.0016/12/2130/03/22
42210150Project 40Tier 1GreenTier 3 Project£ 1,319,565.00£ 214,111.00£1,105,454.0006/12/2128/07/22
Project Status Summary
Cell Formulas
RangeFormula
C3:C42C3=IFERROR(VLOOKUP(TEXT($A3,"######"),TblPPMEx,3,FALSE),"")
D3:D42D3=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: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))
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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:
 
Upvote 0
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:
 
Upvote 0
Maybe something like this:
Excel Formula:
=IF('Project Status Summary'!H3<-2500,3,IF('Project Status Summary'!H3<-1000,2,1))
 
Upvote 0

Forum statistics

Threads
1,223,157
Messages
6,170,420
Members
452,325
Latest member
BlahQz

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