Exceljet

Quick, clean, and to the point

If NOT this or that

Excel formula: If NOT this or that
Generic formula 
=IF(NOT(OR(A1="red",A1="green")),"x","")
Summary 

To do something when a cell is NOT this or that (i.e. a cell is NOT equal to "x", "y", etc.) you can use the IF function together with the OR function to run a test. In cell D6, the formula is:

=IF(NOT(OR(B6="red",B6="green")),"x","")

which returns "x" when B6 contains anything except "red" or "green", and an empty string ("") otherwise. Notice the OR function is not case-sensitive.

Explanation 

The behavior of the IF function can be easily extended by adding logical functions like AND, and OR, to the logical test. If you want to reverse existing logic, you can use the  NOT function.

In the example shown, we want to "flag" records where the color is NOT red OR green. In other words, we want to check the colors in column B, and take a specific action if the color is any value other than "red" or "green". In D6, the formula were using is this:

=IF(NOT(OR(B6="red",B6="green")),"x","")

In this formula, the logical test is this bit:

NOT(OR(B6="red",B6="green"))

Working from the inside out, we first use the OR function to test for "red" or "green":

OR(B6="red",B6="green")

OR will return TRUE if B6 is "red" or "green", and FALSE if B6 contains any other value.

The NOT function simply reverses this result. Adding NOT means the test will return TRUE if B6 is NOT "red" or "green", and FALSE otherwise.

Since we want to flag items that pass our test, we need to take an action when the result of the test is TRUE. In this case, we do that by adding an "x" to column D. If the test is FALSE,  we simply add an empty string (""). This causes an "x" to appear in column D when the value in column B is either "red" or "green" and nothing to appear if not.*

You can extend the OR function to check additional conditions as needed.

*If we didn't add the empty string when FALSE, the formula would actually display FALSE whenever the color is not red.

Increase price if color NOT red or green

You can extend the formula to perform a calculation instead of just returning a fixed value.

For example, say you want to increase all colors except red and green by 15%. In that case, you could use this formula in column E to calculate a new price:

=IF(NOT(OR(B6="red",B6="green")),C6*1.15,C6)

The test is the same as before, the action to take if TRUE is new. 

IF function example - increase price if color is anything but red or green

If the result is TRUE, we multiply the original price by 1.15 (to increase by 15%). If the result of the test is FALSE, we simply output the original price.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.