Combining AND, OR, NOT

Why You Need Multiple Conditions

A single condition is rarely enough for real business rules. In practice, validation logic often sounds like this:

• "Accept this only if X is true and Y is true";
• "Accept this if X or Y is met";
• "Accept this only if X is not the case".

Excel's AND, OR, and NOT functions let you combine multiple logical checks into a single formula — which is exactly what custom validation needs.

AND — All Conditions Must Be True

AND returns TRUE only when every condition inside it is met. If even one fails, the whole formula returns FALSE and the entry is rejected.

Syntax: =AND(condition1, condition2, ...)

Example — Unit Price within a valid range for its category:

The all products should be priced between 150 and 2000. Assuming Unit Price in column I:

=AND(I2>=150, I2<=2000)

Both boundaries must be satisfied simultaneously — too low or too high, and the entry fails.

OR — At Least One Condition Must Be True

OR returns TRUE when any condition inside it is met. Only when all conditions fail does it return FALSE.

Syntax: =OR(condition1, condition2, ...)

Example — Discount is allowed only for specific regions:

Discounts are permitted in the East or West regions only. Assuming Region is in column C and Discount % in column J:

=OR(C2="East", C2="West", J2=0)

This reads: accept the entry if the region is East, or West, or if no discount is applied at all.

NOT — Reverses the Result

NOT flips TRUE to FALSE and FALSE to TRUE. Use it when it's easier to define what's not allowed than what is.

Syntax: =NOT(condition)

Example — Status cannot be Closed if End Date is empty:

=NOT(AND(M2="Closed", L2=""))

This reads: reject the entry if Status is Closed AND End Date is blank. Any other combination is accepted.

Combining All Three

The real power comes from nesting these functions together. There's no limit to how deep you can go — just keep the logic readable.

Example — Quantity must be positive AND discount must be within allowed range:

=AND(H2>0, OR(J2=0, AND(J2>=5, J2<=30)))

This reads: quantity must be greater than zero, and the discount must either be zero or fall between 5 and 30.

A Few Practical Tips

• Build incrementally — test each condition separately before combining them;
• Use a helper column while building — paste your formula in an empty column first to see TRUE/FALSE results row by row before locking it into validation;
• Keep it readable — if the formula exceeds 3–4 conditions, consider whether a helper column or a simpler rule split across two validations would be clearer.

Task

1. Apply an AND validation to the Unit Price column:

   • Formula: =AND(I2>=50, I2<=5000)
   • Error message: "Unit Price must be between 50 and 5000"

2. Apply an OR validation to the Discount % column:

   • Formula: =OR(C2="East", C2="West", J2=0)
   • Error message: "Discounts are only allowed for East and West regions"

3. Apply a NOT validation to the Status column:

   • Formula: =NOT(AND(M2="Closed", L2=""))
   • Error message: "Status cannot be Closed while End Date is empty"

4. Test each rule with both valid and invalid entries — confirm all three behave as expected;

5. In an empty column, paste the formula from task 3 as a helper and observe the TRUE/FALSE output row by row before removing it.