Multiple Condition IF / SWITCH formula's

[mLife9320]

Hi, I'm trying to auto populate a cell with various "text" based on a series of conditions. I'm looking for both the IF and SWITCH formula’s for scenario below:


If [A1 - Work Complete] >= 100 return, “Done”

If [E1 - Constraint] = “Risk” return “Risk”

If [E1 - Constraint] = “Issue” return “Issue”

If [E1 - Constraint] = “Dependency” return “Blocked”

If [E1 - Constraint] = “Decision” return “Pending”

If [A1 - Work Complete] <=99 and [D1 Finish date] >=1 return “Delayed”

If [A1 - Work Complete] =0 and [C1 Start date] >=1 return “Delayed”

If [A1 - Work Complete] >1 and <99 and [D1 Finish date] <1 return “In Progress”

If [A1 - Work Complete] >1 and <99 and [C1 Start date] <1 return “In Progress” <don't think I need this (covered above)>

If [A1 - Work Complete] =0 and [C1 State date] <=1 return “Not Started”

Its to use in MS project, as well as Excel. Thanks

 

[JohnnyMangusso]

Hello.

Have you considered using a Select Case statement?

 

[mLife9320]

Hello.

Have you considered using a Select Case statement?

Hi,

No ... I should have said, I'm an absolute notice when it comes to formula's. I need it to work in Excel and MS Project (Custom Fields)

 

[mLife9320]

Hi,

No ... I should have said, I'm an absolute notice when it comes to formula's. I need it to work in Excel and MS Project (Custom Fields)

*Novice lol

 

[mLife9320]

Hi, I'm trying to auto populate a cell with various "text" based on a series of conditions. I'm looking for both the IF and SWITCH formula’s for scenario below:


If [A1 - Work Complete] >= 100 return, “Done”

If [E1 - Constraint] = “Risk” return “Risk”

If [E1 - Constraint] = “Issue” return “Issue”

If [E1 - Constraint] = “Dependency” return “Blocked”

If [E1 - Constraint] = “Decision” return “Pending”

If [A1 - Work Complete] <=99 and [D1 Finish date] >=1 return “Delayed”

If [A1 - Work Complete] =0 and [C1 Start date] >=1 return “Delayed”

If [A1 - Work Complete] >1 and <99 and [D1 Finish date] <1 return “In Progress”

If [A1 - Work Complete] >1 and <99 and [C1 Start date] <1 return “In Progress” <don't think I need this (covered above)>

If [A1 - Work Complete] =0 and [C1 State date] <=1 return “Not Started”

Its to use in MS project, as well as Excel. Thanks

A Work Complete​	B Task Name​	C Start​	D Finish​	E Constraint​	F Alert​
100%	Task 1	22/03/2022​	23/03/2022​
80%	Task 2	23/03/2022​	24/03/2022​	Risk
0%	Task 3	24/03/2022​	25/03/2022​
23%	Task 4	25/03/2022​	26/03/2022​	Dependency
12%	Task 5	26/03/2022​	27/03/2022​	Decision
0%	Task 6	21/03/2022​	28/03/2022​
30%	Task 7	21/03/2022​	22/03/2022​
1%	Task 8	28/03/2022​	29/03/2022​	Issue
99%	Task 9	29/03/2022​	30/03/2022​
34%	Task 10	30/03/2022​	31/03/2022​