Nested IF Statement Resists a Simple Change

mlefcowitz

New Member
Joined
Sep 3, 2012
Messages
1
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
GHIJKLM
1Project ImpactRisk AreaSymptomsTriggersRisk Response StrategyResponse StrategyContingency Plan
2EXAMPLE: If required skills are not identified or obtained, project schedule may slip and possibly restrict the accomplishment of project goals.Project Resources Budget ScheduleEXAMPLE: 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.MitigationEXAMPLE: 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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,236
Office Version
  1. 365
Platform
  1. Windows
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
Excel Formula:
=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

Active Member
Joined
Jan 13, 2021
Messages
289
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
You might try using a lookup table:
MrExcelPlayground2.xlsm
KLMNOP
1inputinputinputResultTable of results
2CompletedHighMediumMediumCompletedHighHighHigh
3CompletedHighMediumMedium
4CompletedMediumHighLow
Sheet37
Cell Formulas
RangeFormula
N2N2=VLOOKUP(K2&L2&M2,O2:P4,2,FALSE)
 

Forum statistics

Threads
1,141,043
Messages
5,703,915
Members
421,321
Latest member
blusky4

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
Top