Excel 2019: Handle Multiple Conditions in IF
August 01, 2019 - 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
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