Nested IF Statement Resists a Simple Change

[mlefcowitz]

I have a nested IF statement that works, yet when I change a single True value text it suddenly doesn't work and is not recognized as a valid formula:

=IF(OR(AND(B2<>"Completed",C2="High",D2="High"),AND(B2<>"Completed",C2="High",D2="Medium"),AND(B2<>"Completed",C2="Medium",D2="High")),"High",IF(OR(AND(B2<>"Completed",C2="High",D2="Low"),AND(B2<>"Completed",C2="Medium",D2="Medium"),AND(B2<>"Completed",C2="Low",D2="High")),"Medium",IF(OR(AND(B2<>"Completed",C2="Medium",D2="Low"),AND(B2<>"Completed",C2="Low",D2="Low"),AND(B2<>"Completed",C2="Low",D2="Medium")),"Low",IF(B2="Completed","Low",""))))

I want to change it to the last statement from "Low" to "Completed:

=IF(OR(AND(B2<>"Completed",C2="High",D2="High"),AND(B2<>"Completed",C2="High",D2="Medium"),AND(B2<>"Completed",C2="Medium",D2="High")),"High",IF(OR(AND(B2<>"Completed",C2="High",D2="Low"),AND(B2<>"Completed",C2="Medium",D2="Medium"),AND(B2<>"Completed",C2="Low",D2="High")),"Medium",IF(OR(AND(B2<>"Completed",C2="Medium",D2="Low"),AND(B2<>"Completed",C2="Low",D2="Low"),AND(B2<>"Completed",C2="Low",D2="Medium")),"Low",IF(B2="Completed","Completed",""))))

I have run this through the Ants & Apps Excel Checker, and it shows the correct string, "Completed".

What am I not seeing?

RTM_June 2021.xlsx
	G	H	I	J	K	L	M
1	Project Impact	Risk Area	Symptoms	Triggers	Risk Response Strategy	Response Strategy	Contingency Plan
2	EXAMPLE: If required skills are not identified or obtained, project schedule may slip and possibly restrict the accomplishment of project goals.	Project Resources Budget Schedule	EXAMPLE: Schedule approaches the required start date with no identification of required skill sets.	EXAMPLE: Four weeks prior to scheduled start date if no resource is identified with required skill set implement contingency plan.	Mitigation	EXAMPLE: Find internal resource that meets required skill set or train existing resources on LINUX.	EXAMPLE: Find resource that meets required skill set through external hiring sources.
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
Risk Matrix

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Both those formulae work for me, although you can simplify it like

=IF(B2="Completed","Completed",IF(OR(AND(C2="High",D2="High"),AND(C2="High",D2="Medium"),AND(C2="Medium",D2="High")),"High",IF(OR(AND(C2="High",D2="Low"),AND(C2="Medium",D2="Medium"),AND(C2="Low",D2="High")),"Medium",IF(OR(AND(C2="Medium",D2="Low"),AND(C2="Low",D2="Low"),AND(C2="Low",D2="Medium")),"Low",""))))

 

[JamesCanale]

You might try using a lookup table:

MrExcelPlayground2.xlsm
	K	L	M	N	O	P
1	input	input	input	Result	Table of results
2	Completed	High	Medium	Medium	CompletedHighHigh	High
3					CompletedHighMedium	Medium
4					CompletedMediumHigh	Low
Sheet37

Cell Formulas
Range	Formula
N2	N2	=VLOOKUP(K2&L2&M2,O2:P4,2,FALSE)