Sometimes, you might need to write a formula that uses the IF function to test for this OR that, or this AND that.
There are two special functions, AND and OR that make this easy to do.
Let’s take a look.
In this first worksheet, we have a list of employees. Let’s assume that you need to group these employees into two groups. Group A includes employees in Sales and Marketing and group B includes employees in Fulfillment, Support, or Engineering.
While this could be done using nested IF statements, an easier way to calculate these groups is to use the IF function together with the OR function.
Start off normally with IF and an open parentheses. For the logical test, we want use the OR function, which simply accepts a series of logical tests.
If any test returns TRUE, the OR function will return TRUE.
I can write the first logical test as “F5 = Sales”, then add a comma, and write the second test as “F5 = Marketing”. Then I close the parentheses for OR.
For value if true, I’ll use A, and for the value if false, I’ll use B. When I copy the formula down, we’ll get the groups we need.
The way this works is that the OR function behaves like a single logical test for the IF function….it returns true if F5 is either Sales or Marketing.
Now let’s look another example, which contains a list of houses for sale. Imagine that you have a big list and you want to quickly mark the properties you’re interested in. You’re looking for a house that has 3 or more bedrooms, is less than 3000 sq feet, and was built after 1979.
In this case, we can’t use IF with OR, we need to use IF with the AND function.
The AND function works like OR function…simply enter each logical test separated by commas. In this case, D5 needs to be greater than or equal to 3, F5 needs to be less than 3000, and G5 needs to be greater than 1979.
For the value if true, I’ll simply use “Check”, and for value if false, I’ll use empty double quotes, which will appear like blank cells on the worksheet.
When I copy the formula down, you can quickly see which houses meet the criteria.
Like the OR function, the AND function evaluates to a single TRUE or FALSE VALUE. However, unlike OR, the AND function requires that all tests return TRUE.