Nested IF Statement

[AliGeeJay]

I am looking for some advice of nested IF statements.

I am looking to put an IF statement in Cell C2 with the following conditions:

If Cell A2 <10 and Cell D2 = Compliant then “ “ but, if Cell A2 is between 10 and 50 refence Cell E5, but if it is >50 then reference Cell E4.

However if Cell A2 <10 and Cell D2 = Investigate then reference Cell E3 but, if Cell A2 is between 10 and 50 reference Cell E5, but if it is >50 then reference Cell E4.

Finally, if Cell D2 = Concurrent then reference Cell E4.

I hope my outline above makes sense.

I have attached a spreadsheet for reference of what the outcome would look like.

Con_Sample.xlsx
	A	B	C	D	E
1	Threshold %			RAG Status
2	9	%		Compliant
3	35	%	✅	Compliant	?
4	-12	%	?	Investigate	✅
5	15	%	⚠	Concurrent	⚠
Sheet1

Cell Formulas
Range	Formula
C5	C5	=IF(A5<0,"",IF(OR(D5="Concurrent",D5="Investigate",D5="Compliant"),IF(OR(A5>10,A5<50,),E5,"")))

 

[Fluff]

How about

=IF(D2="Concurrent",$E$4,IF(A2<10,IF(D2="compliant","",$E$3),IF(A2<=50,$E$5,$E$4)))

 

[AliGeeJay]

How about

=IF(D2="Concurrent",$E$4,IF(A2<10,IF(D2="compliant","",$E$3),IF(A2<=50,$E$5,$E$4)))

Worked like a charm. Many thanks for the help.

 

[AliGeeJay]

How about

=IF(D2="Concurrent",$E$4,IF(A2<10,IF(D2="compliant","",$E$3),IF(A2<=50,$E$5,$E$4)))

Hello again, in order take into account the Investigate section do I need to copy the section for Compliant and change this to Investigate?

 

[Fluff]

No need to change anything, it should do that already.

 

[AliGeeJay]

No need to change anything, it should do that already.

Strange, it is not picking up on the cell in Column D that has an entry of Investigate.

 

[Fluff]

It works for me, unless I have misunderstood something.

Cond_Sample.xlsx
	A	B	C	D	E
1	Threshold %			RAG Status
2	9	%		Compliant
3	35	%	⚠	Compliant	?
4	-12	%	?	Investigate	✅
5	-15	%	✅	Concurrent	⚠
6	12	%	⚠	Compliant
Sheet1

Cell Formulas
Range	Formula
C2:C6	C2	=IF(D2="Concurrent",$E$4,IF(A2<10,IF(D2="compliant","",$E$3),IF(A2<=50,$E$5,$E$4)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B6	Cell Value	<0	text	NO
A6	Cell Value	<0	text	NO
B2:B5	Cell Value	<0	text	NO
A2	Cell Value	<0	text	NO
A3:A5	Cell Value	<0	text	NO

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: If Statement Headache... Again
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[6StringJazzer]

It works for me, unless I have misunderstood something.

Your formula matches the description in the OP but his example is his first post is not consistent with his description.

Finally, if Cell D2 = Concurrent then reference Cell E4.

But the example shows that case referencing E5.

 

[Fluff]

Agreed, I was just going by the description. Have to see if there is any further feedback from the OP.