All language subtitles for 1. Logical Functions (AND, OR, IF)

In this section of the course, we're going to dive into the magical world of using logical functions

in Excel because logical functions are some of the most important functions that you can know or have

in your toolkit when you're working in Excel, particularly if you're an intermediate user.

And what logical functions help us do is basically make decisions.

Now there are quite a few different logical functions.

You'll find them on the Formulas tab in the logical group of the function library, and you can see

all of them sitting in there.

And we're going to use quite a few of these throughout the balance of this section.

But what I wanted to make sure that we do in this first lesson is just do a quick recap on some of those

basics just to make sure that were all on the same page.

And we're starting from the same baseline because it's really important to understand the concept behind

how logical functions work before we can move on to more complex calculations using things like ifs,

some ifs count ifs and nested if statements.

So let's talk about logical statements in their most basic form, first of all.

And we're going to do this using an example, because it's always the best way to visualize this.

So on this first worksheet, I have a very small table and this lists out some expenses.

So we have the person's name in here.

We have the total of their expenses.

And what I want to do is work out if an approval is required.

Now we have some additional information next to this that says expenses over the following amount must

be approved.

So if the expense is over a thousand dollars, then it needs to be approved by a manager.

So I can work this out by using a very simple, logical statement.

Now what exactly are logical statements?

Well, it's basically like performing a test.

So in this particular example, if we take the first expense just here, I would want to construct a

logical statement that says if this value in Selby five is greater than or equal to this value in Cell

G five, then it needs to be approved.

And I want to perform that same test on all of these expense invoices.

Now, when we're doing things like this, we use what we call operators and you can see I've just paste

it in a little picture of a table that shows different operators that you might be using in your logical

statements.

So things like equal to greater than less than greater than or equal to so on and so forth.

So let's do this first one just here.

We're going to type in equals tool in Excel.

No, we're about to do a formula and this is very straightforward.

We perform our test.

So is the value in Cell B five greater than or equal to?

We're going to say that because if it's a thousand dead on, it needs to be approved.

Is it greater than or equal to the value in Cell G five?

Now remember, if you want to copy this formula down, we don't want that figure in Cell G five moving,

so we need to lock it by pressing the four key and making it absolute look.

If I hit enter just here is giving me a result of false.

And what I can do is I can now copy that formula down and I can see here that all of the results showing

as true on the invoices that are greater than or equal to a thousand and need to be approved.

Now notice here that all of my answers are either true or false.

And if you perform a logical calculation in this particular way, using these operators, the output

is always going to be true or false.

Now that might be absolutely five or whatever it is that you're doing, but sometimes it might be that

you want to make this more meaningful.

So instead of it saying false, just here, I might want it to say OK.

Instead of saying true, just here, I might want it to say approval.

So effectively, what I might want to do is attribute meaning to the true and false values.

And I'm going to show you how you can kind of move on from this and do that in the next lesson.

But for now, let's just get this concept of logical formulas straight in our head.

We perform a test now because of the way that I've set this up.

If this threshold in Cell G five was now to change, so maybe this now changes to twelve hundred.

Notice that all of my results update if I was to change this to 500 and enter, everything is now true.

Okay, so a really nice, effective way of putting this formula in and making it dynamic and easy to

update now just to go back to changing these values and adding meaning to them in order.

To do that, instead of just having a basic, logical function, we would need to turn this into an

if statement.

And that's basically what an if statement allows us to do.

It's basically this same logical formula, but it allows us to attribute more meaning to the results

and we're going to cover if and a lot more detail in the next lesson.

But let me just show you how we would change these values.

Now I'm just going to delete these out.

Now what we're going to do is we're going to wrap basically our logical statement inside an if statement.

Now notice here it says check whether a condition is met and returns one value, if true and another

value, if false.

So the first argument here is our logical test.

So our logical test is pretty much what we just did.

Is this value greater than or equal to this value at full to lock press comma to move on to the next

argument?

This is where we can attribute meaning to the true or false results.

So it says value, if true, I can now define what I want it to say if the result is true.

So if the result is true, effectively the number is greater than a thousand and it has to be approved.

So in quote marks because text in formulas must always be in quote marks, I wanted to say approval.

Comma.

If the value in the cell is false, I don't want it to do anything, I just wanted to say, OK, let's

close the bracket.

So I've attributed meaning to the true and false results.

Let's say enter.

And I can now double click to copy that down.

And it's a lot clearer for me to see which invoices need to be approved.

So simply by adding in that if statement, we've changed the rather generic.

True or false output to something that's more meaningful and easier to understand.

And that's basically what an IFF does for us.

Let's look at a few more examples of if in action now in this first table, again, very small table,

we have some test scores, so we've got some student names and we've got the score they achieved in

a test and the pass mark is 85.

So I want to create an if statement that says if the value in this cell is greater than or equal to

the value in this cell 85, I want a result of pass.

If it's not, I want a result of fail.

So again, we're going to use if our logical test is if the value in before is greater than or equal

to the value in H3, we're going to copy this down.

So f to lock.

If that is true, then they have passed.

Hooray.

If it's false, then unfortunately they have failed to close the bracket.

I'm going to do a control enter to stay in the same cell.

Copy that down and I get my results so much more meaningful than simply true or false.

Let's take a look at another example of if I'm going to add in a little bit more complexity just here.

So in this second table, we have some products, we have the weight in kilos and then we have the price

for those products.

And I'm actually just going to change these to dollars just to keep everything in this spreadsheet consistent.

Now what we're going to say here is that if the weight of the product is greater than or equal to 30

kilos, then there's going to be a 20 percent shipping fee and that shipping fee is going to be 20 percent

of the price.

So let's constructeur if what is our logical test?

Well, if the weight is greater than or equal to 30 kilos again, F4 to look now, what do we want it

to do if that is true?

So if the product is greater than or equal to 30 kilos, well, there's going to be a 20 percent shipping

fee.

So our true result is going to be a calculation because we want it to work out that shipping fee.

So if it's true, we want to do the price.

Multiplied by 20 percent.

Four to lock coma if it's false.

There's going to be no shipping fee, so we're going to put a zero on the end there.

Close our bracket control answer to stay in the same cell.

And if I copy this down, I should find that the only two products that have a shipping fee are the

ones that are greater than or equal to 30 kilos.

And this amount should be 20 percent of the actual price, so we can incorporate formulas into our logical

if statements as well.

Now we just want to finish off this lesson by running through a couple of other basic logical statements.

And that is and and all now well and allows you to do is basically perform two logical tests.

So we're taking this example of test results again.

So we have our students and we have the score they achieved in Test one and the score they achieved

in Test two.

And what we're going to say is that they need to have achieved above 75 in Test one and above 65 in

Test two in order to get a result of pass.

So we're effectively performing two logical tests here.

So if we want to do more than one and we need both of these to be true to get a positive result, we

use the and formula.

Notice the arguments.

Logical one, logical two.

So our first logical test is if this score is greater than or equal to the pass mark for Test one f

for too long.

And the second test score is also greater than or equal to the pass mark for tests to f for to lock.

Both of those have to be true.

In order to pass now, I'm going to enter.

It's going to give me a result of true or false.

If I want to add meaning to this and have pass or fail, I would need to edit this formula and wrap

it in an if statement.

Remember, that is how we add meaning.

The first argument for our if statement is the logical test.

Well, those are being generated by and formula so I can go straight to the end and just define what

I wanted to say.

So if both of those are true, it's going to be a pass.

If both or one of those are false, it's going to be a fail.

Close the bracket and sir.

And now I've combined two functions together to perform two logical tests, and I've given them meaning

using if all works in a very similar way.

Except what we're saying here is that they need to have achieved the pass mark in Test one or in Test

two in order to achieve a result of pass.

So we're going to go straight in and type in a formula, a logical test.

Well, we want to generate this using our or calculation logical one.

Logical two.

So if score one is greater than or equal to the pass mark.

Four, to lock or score, two is greater than or equal to the pass mark for test to.

Afford to look close off at all.

We're now back into our if statement, we can now set up the value if those are true.

So if that's true, we have a pass.

If it's false, we have a fail control enter and then I can copy that down.

So notice here that the only one that comes up as fail is where both of these schools are less than

the pass marks.

So we've looked at basic, logical functions there.

We've seen and we've seen or and we've seen how we can add meaning by using, if and in the next lesson,

we're going to look at if statements in a lot more detail.