Excel 2020: Handle Multiple Conditions in IF
August 05, 2020 - by Bill Jelen
When you need to do a conditional calculation, the IF function is the answer. It works like this: If <something is true>, then <this formula>; otherwise <that formula>. In the following figure, a simple IF calculates a bonus for your sales of more than $20,000.
But what happens when two conditions need to be met? Most people will nest one IF statement inside another, as shown below:
But this nesting gets out of hand if you have many conditions that have to be met. Use the AND function to shorten and simplify the formula. =AND(Test,Test,Test,Test) is True only if all of the logical tests evaluate to True. The following example shows a shorter formula with the same results.
If you like AND, you might find a use for OR and NOT. =OR(Test,Test,Test,Test) is True if any one of the logical tests are True. NOT reverses an answer, so =NOT(True) is False, and =NOT(False) is True. If you ever have to do something fancy like a NAND, you can use =NOT(AND(Test,Test,Test,Test)).
Caution
Although Excel 2013 introduced XOR as an Exclusive Or, it does not work the way that accountants would expect. =XOR(True,False,True,True) is True for reasons that are too complicated to explain here. XOR counts whether you have an odd number of True values. Odd. Really odd.
Title Photo: Issam Hammoudi at Unsplash.com
This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.
