If Not This Or That Excel Formula

which returns “x” when B6 contains anything except “red” or “green”, and an empty string ("") otherwise. Notice the OR function is not case-sensitive. 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: In this formula, the logical test is this bit: Working from the inside out, we first use the OR function to test for “red” or “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: The test is the same as before, the action to take if TRUE is new. 

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.

Dave Bruns

Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.