Many people are not familiar with the AND or the OR logic functions. Even people who are familiar with them tend to associate them as part of a nested IF statement ; however, you can use them as stand alone formulas.
AND and OR logic functions allow you to test up to 30 conditions.
The syntax for these two functions is:
Function(logical1, logical2.......)
For an AND function to be true ALL conditions must be met whereas with an OR only ONE of the conditions must be met.
Below is a simple example:
I have a customer aging and I want to test it.
In Row 2, I have asked Excel to tell me if the City is Indianapolis AND the State is IN AND the Business is Bicycles OR the Balance is greater than 1000 to display True.
Column G shows the resulting answer and Column H shows the calculation.
All 3 of the conditions within the AND syntax must be correct for the answer to be true OR the amount needs to be greater than 1000.
The formula at row 2 is =OR(AND(B2="Indianapolis",C2= "IN",D2="Bicycles"),E2>1000)
The same formula was used in Rows 3-5 just the information in Columns B through F were changed.
Inside the AND, Excel is testing for 3 conditions. The AND is nested inside of an OR statement.
Row 2 displays True as all 3 of the AND conditions are met. The Balance condition is not met.
Remember for an OR to display as true only one of the conditions needs to be met and the OR had two conditions - the Balance Amount and the AND conditions.
At Row 3, the answer is False because one of the AND conditions was not met. The city displays as Muncie- not Indianapolis and the Balance test was not met.
At Row 4, the answer is False because Column D displays Parts and not Bicycles so all of the AND conditions were not met and the OR test was not met as the balance is not greater than 1000.
At Row 5, the answer displays as True because the balance is greater than 1000 even though none of the AND conditions are met.
By themselves the resulting answer of a AND and OR are only True and False which can be useful in itself as seen above however you can you nest them within an IF and create an even more powerful calculation.
For example, I could take the exact same formula and nest it inside an IF and have it display Current if the conditions are met (True) or Overdue if the conditions are NOT met (False).
=IF(OR(AND(B2="Indianapolis",C2="IN",D2="Bicycles"),E2>1000),"Current","Overdue")
There is also a NOT logic function however that only tests one condition.
If you want to learn more about Decision Making functions, check out our Decision making Excel course.It offers 3 hours of CPE. Click here.