Transcripts for the above video clip:

AND & OR FUNCTIONS

The AND and OR 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.