Hi, this is Jason.
This is the second video in a set of three about LOGIC functions.
In the last video I talked about the AND and the OR functions, but when you combine both
of those into a single formula, the possibilities become endless and this is where the magic
happens.
OK?
In the last video I talked about how to do an AND function and an OR function.
Now, sometimes you have situations where you need to use both of those functions in the
same formula.
Here's an example.
I need to test (or find) anybody who is a Brickie or a Plumber based in Queensland.
So the first bit, the Brickie or the Plumber, that's the OR element.
The AND element is when I find one of those two trades based in Queensland, the trade
has to be true and the state has to be true.
So if you ever get a situation like this, build it up step by step.
So the first thing I'm going to do here is to say Equals OR, open the brackets.
I'm going to check the trade, first of all say, is it equal to "Brickie", comma.
I'm then going to check the trade again to say, is it equal to "Plumber", and close the
brackets.
So that shows TRUE for anybody who is one of those two trades.
OK, so TRUE here, TRUE here, TRUE here and TRUE here.
That's only half the equation though because I'm only interested in people who are doing
one of these two trades, but also based in Queensland.
So here's what I do to extend the formula.
So I'm going to click on the first one.
I'm going to position my cursor between the equal sign and the OR function and introduce
the AND function with it's open brackets.
Now with the AND function, of course, I'm testing two conditions here.
The first one is already written, so it's the bit I already wrote there, the OR function,
which we can see as returning TRUE or FALSE, it's listed here already.
So all I'm going to do is leave that, go to the right hand side and place a comma, and
then add in my second condition for the AND function which is, are they based in Queensland?
So I click the State cell, say is it equal to "QLD" and close the brackets for that.
So the bit I've highlighted there is the original OR function.
The bit that's not highlighted are the two bits I've added.
So I've put Equals AND open bracket at the beginning, I've put the second condition and
the closing bracket at the end.
Now , if both of those bits are TRUE, it's going to show TRUE.
If either of those bits are FALSE, the whole answer is FALSE.
So if we check the TRUE here, you can see that they are a Brickie based in Queensland.
So that's good.
The TRUE here, they're a Plumber based in Queensland, so that's good.
Now anybody who's a Plumber, but based in New South Wales comes up FALSE, or anybody
that's based in Queensland but is not a Plumber or a Brickie, that comes up FALSE as well.
So both bits have to be TRUE.
So just a reminder.
The one function has both an OR element and AND element.
Now sometimes the AND and the OR are not that way round.
So in this case the OR is inside the AND, but there are occasions where the AND needs
to go inside the OR brackets, so you nest it the other way round.
So an example might be: show me any Plasterer who has more than thirty jobs or any Plumber
who has more that ten jobs.
So with all of these things I'm testing, the Plasterer and more than thirty jobs, that's
an AND function.
The Plumber with more than ten jobs, that's another AND function.
And I'm testing if either of those are true.
So in this situation, the OR goes first, it's the outer function and the two AND functions
go inside.
So let me show you what I mean.
So let me write it up here so we don't forget.
So first of all we're checking for any Plasterer who has more than thirty jobs or any Plumber
who has more than ten jobs.
So for this I'm just going to write it all the way through from scratch, left to right.
So I'd say Equals OR, open brackets, then I'm doing an AND function to first of all
test: are they a Plasterer?
And secondly, do they have more than thirty jobs?
Close the brackets for the AND, that's the first AND section done.
Comma.
I'm then doing another AND function where I'm testing the trade to see if it's equal
to "Plumber" and then secondly I'm checking their jobs to see if they're greater than
ten.
Close with two brackets, one for the second AND function, one for the outer OR function.
And that's your formula.
So let's press ENTER.
Let's copy that down.
And all those that are showing TRUE are where one of these two conditions are true.
OK, so they're a Plasterer who have more than thirty jobs or a Plumber who have more than
ten jobs and you could check with the data here, that is true.
So that's just two examples where you can combine AND and OR together.
In the first example, the AND went first and OR was the inner function.
In the second example, the OR went first and there were two inner AND functions.
So you can get creative with this stuff.
And actually the, the options are limitless.
So in the next video I want to show you how to combine the IF function, which is also
a LOGICAL function and use that to tidy up the TRUEs and FALSEs here because in a big
list it's sometimes really hard to spot quickly where the TRUEs are.
You have to scan through quite slowly and quite meticulously to see those.
So I'm going to use the IF function to tidy that up and where it's TRUE, I'm going to
show a marker and where it's FALSE I'm going to show just a blank cell to keep it nice
and clean.
So if that's of interest to you, watch the next video.
I'll put the link on the page.