which returns “x” when B6 contains “red” or “green”, and an empty string ("") if not. Notice the OR function is not case-sensitive. This is an example of nesting – the OR function is nested inside the IF function. Working from the inside-out, the logical test is created with the OR function: OR will return TRUE if the value in B6 is either “red” OR “green”, and FALSE if not. This result is returned directly to the IF function as the logical_test argument. The color in B6 is “red” so OR returns TRUE: With TRUE as the result of the logical test, the IF function returns a final result of “x”. When the color in column B is not red or green, the OR function will return FALSE, and IF will return an empty string ("") which looks like a blank cell: As the formula is copied down the column, the result is either “x” or “”, depending on the colors in column B. Note: if an empty string ("") is not provided for value_if_false, the formula will return FALSE when the color is not red or green.
Increase price if color is red or green
You can extend this formula to run another calculation, instead of simply returning “x”. For example, let’s say you want to increase the price of red and green items only by 15%. In that case, you can use the formula in column E to calculate a new price:
The logical test is the same as before. However the value_if_true argument is now a formula: When the result of the test is TRUE, we multiply the original price in column C by 1.15, to increase by 15%. If the result of the test is FALSE, we simply return the original price. As the formula is copied down, the result is either the increased price or original price, depending on the color.
Notes
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.
