How do I correctly use nested IF, OR, AND, NOT in Excel formulas?

OK, following on from my earlier post, I see you added the logic you're trying to enact.

You're missing an AND() conditional.

In English, you want:

Condition 1: either Budget >200,000 OR Absolute is ≥100,000

condition 2: BOTH Overrun is "Yes" AND Overrun % > 20(although Conditions::$F$2 says 80%, not 20%...?)

so what you have is an OR() statement, plus an AND() statement that both have to return true. That would be:

=IF(
AND(
OR(
B3>Conditions::$B$1,
C3>Conditions::$C$1
),
AND(
E3=Conditions::$E$1,
F3>Conditions::$F$1)
 ),
 Conditions::$G$2,
Conditions::$G$3
)

or:

because you want the OR() condition to be true, AND you want the AND() condition to be true, so there are two AND()s required.

Bear34_1 wrote:

1. Budge (B) is ≥ 200000 OR Absolute (C) is ≥ 100000, (one need to be true) then check

2. Overrun (E) = “Yes” AND Overrun in % (F) >20 (both need to be true)

3. If either Budge (B) or Absolute (C) are True in step 1, AND both Overrun (E) and Overrun in % (F) are true in step 2

4. Return “Flag” for true conditions or “Okay” for false conditions.

I'm not sure the example makes a lot of sense in the real world!

1. You want to flag if the budget (B) or YTD (C) are large, even if there is no Overrun (E, F)?
2. If there is a % Overrun (F) then how can Overrun (E) be false? Why not just test for % Overrun (F)?
3. That in your "step 3" contains more conditions than 1. 1 will override it in some cases, i.e. 1 will evaluate to true so it doesn't matter whether 3 is true or false.
4. The part of the formula that returns text as the result.

My advice: if you find yourself twisted up in "nested" logic in complicated formulas then there is almost always a way to clarify the logic and simplify.

Restate the problem in "plain English" (or whatever language you are using).

Take advantage of modern functions specifically designed to minimize complicated nesting.

Have you had a look at IFS?

IFS - Apple Support

And SWITCH?

SWITCH - Apple Support

SG

Sometimes it helps to break down the formula to understand what it's doing.

Let's look at the Legal line since you've highlighted that as in error.

In this case, the formula you have can be formatted as:

So, this will return TRUE (or, in this case 'FLAG') if ANY of these three conditions are true:

B6 > Conditions::$B$1 => FALSE because 55,00 is less than 200,000

C6 > Conditions::$C$1 => TRUE because 105,000 is greater than 100,000

At this point, the AND doesn't matter because you've already met the threshold of the OR statement (one or more of the conditions are TRUE). However, for completeness:

E6 = Conditions::$E$1 (FALSE) AND F6 > Conditions::$F$1 (FALSE)

So the AND() statement returns FALSE (these two conditions are not TRUE), however, the earlier statement already returned TRUE, so this doesn't matter.

So the big question is, what DO you want it to check on? What is the logic that you're trying to instigate here?

When the logic in a formula starts to get tangled sometimes it helps to step back and try to express it "in plain English."

In plain English I think your current formula says this:

Flag if the Budget is greater than 200,000 OR if the YTD is greater than 100,000 OR if Overrun is "Yes" and the % Overrun > 80%.

Is that what you mean?

I'm not sure how it is possible to have "No" for Overrun and still have a % Overrun in some cases.

Also, none of your departments meet the test for an 80% overrun. Do you intend the overrun to be that high.

Perhaps you want to test for the size of the Budget or YTD. If either is big enough, then check if the Overrun is over a certain percentage and if it is, flag it?

SG

Take a look at this recent thread: How to apply conditional highlighting to … - Apple Community