How to use the AND OR Excel functions to perform logic tests. This is particularly useful with regards to the IF function.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
- - 04:21
- - Skill Level: 4
- - What's my skill level
AND OR excel functions
The AND OR Excel functions allow you to investigate a number of cells and generate a true or false, depending on whether they meet all, or some of the criteria respectively.
In this segment you’ll learn how to activate and use the AND and OR functions.
In this example we have received the marks from a number of students who are looking for jobs at our company. We have a policy, that if all three subjects are above 75 %, we accept them immediately, if any one of them is above 75% we do an interview, and if none of them are above 75%, we reject them.
Excel helps us out with the AND and the OR function, so for example, in the first criteria, we accept them immediately if the Math’s mark, and the Science, and the English mark are above the required marks.
so you can click on the cell,
activate the function wizard
find the AND command, and say ok
and here we can say is, the first logical test is if this cell, the Math’s mark, is bigger than the required mark
and we are going to make the required mark absolute, so we can copy it down later.
The second criteria is we want the Science mark to also be bigger that 75%,
And the 3rd criteria is we want the English mark to be bigger then the 75%,
when we click ok, we see that for this student, it is false
If we copy this all the way down, you’ll see only places where all 3 of the marks are above the required marks will there be a true
To determine whether we should interview them first, our criteria is that any one of these, can be above the 75%, and in that case we can use the OR function,
so you click on the cell ,
activate the function wizard
and find the OR function,
and say ok,
and now the logic test is, if the Math’s mark is bigger than 75%, and we make it absolute,
OR if the Science mark is bigger than 75%,
OR the if the English mark is bigger than 75% – then you want to see a true.
And when we copy this down, you’ll see that, in the cases where all three are above, the AND situation, and the OR situation both come through as a true.
But where there is a case where one of the marks is above 75%, the interview first will come through as a true.
The AND, and the OR function, can be used very effectively together with IF functions. So in this situation, we know that if there’s a false on this one, we are going to reject them, and if there’s is a true on either one, we need to follow up.
So we can build a IF statement by;
going function wizard,
IF,
ok,
and the logical test here is, OR that equals true comma,
OR that equals true,
So what we are saying is, if either of those are true, then we want to follow it up, but if it’s false, then we are going to reject the application.
And now when you copy it down, you can quickly see who you need to follow up on and who you need to reject.