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 to 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" and 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 see 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 at 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 square 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 the 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 the value if false, I'll use empty double quotes which will appear as 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.
The IF function runs a logical test and returns one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions. The IF...
The OR function is a logical function to test multiple conditions at the same time. OR returns either TRUE or FALSE. For example, to test A1 for either "x" or "y", use =OR(A1="x",A1="y"). The OR function can be used as the logical test inside the...
The Excel AND function is a logical function used to require more than one condition at the same time. AND returns either TRUE or FALSE. To test if a number in A1 is greater than zero and less than 10, use =AND(A1>0,A1...