Afrikaans
Akan
Albanian
Amharic
Arabic
Armenian
Azerbaijani
Basque
Belarusian
Bemba
Bengali
Bihari
Bosnian
Breton
Bulgarian
Cambodian
Catalan
Cebuano
Cherokee
Chichewa
Chinese (Simplified)
Chinese (Traditional)
Corsican
Croatian
Czech
Danish
Dutch
English
Esperanto
Estonian
Ewe
Faroese
Filipino
Finnish
Frisian
Ga
Galician
Georgian
German
Greek
Guarani
Gujarati
Haitian Creole
Hausa
Hawaiian
Hebrew
Hindi
Hmong
Hungarian
Icelandic
Igbo
Indonesian
Interlingua
Irish
Italian
Japanese
Javanese
Kannada
Kazakh
Kinyarwanda
Kirundi
Kongo
Korean
Krio (Sierra Leone)
Kurdish
Kurdish (Soranรฎ)
Kyrgyz
Laothian
Latin
Latvian
Lingala
Lithuanian
Lozi
Luganda
Luo
Luxembourgish
Macedonian
Malagasy
Malay
Malayalam
Maltese
Maori
Marathi
Mauritian Creole
Moldavian
Mongolian
Myanmar (Burmese)
Montenegrin
Nepali
Nigerian Pidgin
Northern Sotho
Norwegian
Norwegian (Nynorsk)
Occitan
Oriya
Oromo
Pashto
Persian
Polish
Portuguese (Brazil)
Portuguese (Portugal)
Punjabi
Quechua
Romanian
Romansh
Runyakitara
Russian
Samoan
Scots Gaelic
Serbian
Serbo-Croatian
Sesotho
Setswana
Seychellois Creole
Shona
Sindhi
Sinhalese
Slovak
Slovenian
Somali
Spanish
Spanish (Latin American)
Sundanese
Swahili
Swedish
Tajik
Tamil
Tatar
Telugu
Thai
Tigrinya
Tonga
Tshiluba
Tumbuka
Turkish
Turkmen
Twi
Uighur
Ukrainian
Urdu
Uzbek
Vietnamese
Welsh
Wolof
Xhosa
Yiddish
Yoruba
Zulu
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.
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.