In this video we look at how to combine the IF function with the OR and AND functions to test more than one condition at the same time.
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.
The IF function can perform a logical test and return 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 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...
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...
I sincerely want to register my profound gratitude to Dave and Lisa. There are many features so unique about the tutorials in Exceljet: the simplicity, down-to-earth approach and the connection between the instructor and the learner. -John