(upbeat music)
- Sometimes when you're using the IF function,
you need to check for multiple criteria.
In other words, the logical test has to be expanded.
That's the first portion,
the first argument in an IF function.
This organization has decided to give bonuses
to people who have a job rating of four or five,
however, they must be full-time.
So two conditions must be met.
We're about to use the "and" function
along with the "if" function.
A companion function is called "or",
and to a lesser extent, a function called "not"
could also be used.
We'll see examples of all three.
Column I doesn't really have to be wider,
but let's make it wider so we can see
the function clearly here.
Equal if, we need to check for two conditions,
we use the word "and."
Sometimes we use and with three or four or five.
We can actually use it up to 127 conditions.
And in no particular order, we're saying,
the job rating that's in cell J2 here,
needs to be greater than three,
and the status it's in cell F2,
must be equal to Full Time.
This is a text entry.
So we need to embed this within double quotes.
So a double quote Full Time.
Full Time has a space in it
must be in the example here,
the upper lower case oddly enough, isn't critical,
but why not make it look the same as the entries here?
Right, parenthesis.
There are four possibilities.
The first entry here can be true.
And the second one can be true,
but one could be true and false,
or false true or false false.
So four possibilities here.
Only when both are true,
is the answer going to be 3000.
So we put in a comma 3000,
both need to be true for 3000 to be the answer.
When either one of those is not true,
comma, the answer is perhaps zero,
or we could put in a blank with double quote, double quote.
Closing, right parenthesis here,
I'll press Control + Enter, copy this down the column.
And as you can clearly see, as we expose the form again,
only when the job reading is good
and the status is full time, is there a bonus here.
Now, if we were to use the word "or,"
and we could in this case,
I think you know, immediately what's going to happen.
If we use "or" we're saying if either condition is true.
It's going to be a more popular decision that's for sure.
But if either condition is true.
So out of the four possibilities here,
only when we see false false will be no bonus.
So I'll just press Control + Enter here
to change all of these.
And we see what's happening there.
If you know your data well, as we've been saying,
some of these examples here,
these fall into place a bit more readily
than you might think.
But let's change this in a different way.
It gets a little more complex or can be at times
if the need arises.
The company changes its mind again and says the following.
"We've decided that if you've got a good job rating,
we don't care what your status is.
We will give you the bonus."
So let's look at this "or" function
in a different way.
Either this is true, or sometimes what you'll see
is an "and" inside of an "or."
Or in some cases an "or" inside of an "and."
What are we saying here?
What are we about to say?
If your job reading is not good enough,
we'll still give you the bonus if,
your status is full time,
and your years of service
that's in cell E3 here,
is greater than nine.
Another right parenthesis required.
Notice how the parentheses are color coded.
For every left there must be a right
and the color stay in sync there.
We're about to change this.
I'll press Control + Enter here
and make that column wider and make this even better.
I'll zoom in a bit,
holding down the Control key in the mouse wheel
and then moving to the right
so we can see all the relevant cells.
What are we saying now?
You will get the bonus automatically
if your job rating is four or five,
and we see that happening in a number of cases,
but if you don't have a job rating a four or five,
you can still get the bonus because
this is the second part of the "or" structure.
If this is true, you still get the bonus.
So some people might be full-time and have nine years.
They're going to get the bonus regardless of,
possibly bad job rating.
So that's happening too.
And we can see this in row eight, for example
looking at the data right here.
Off to the right job rating of one,
off to the left full time 13 years.
So in a variety of different ways,
you can use "or" and "and".
And again, it's not necessarily
aimed toward more complexity,
but to be able to cover those cases that are needed.
Now, the word not can also be used.
I tend to shy away from it.
It tends to be harder to read,
but what if somehow or other this company changes it's mind
again and says, "We don't give bonuses to hourly people."
And we want to say to make it a real simple example.
What if we say, if your status is not hourly,
you will get the bonus.
It's put in the word, not right here.
And in here, we'll simply focus on the idea
that status is equal to hourly.
But we put the word not in front of it.
And we can do this in other ways too
using different symbols there.
By the way, that upper lower case, again,
not critical, but it looks better this way.
Could have used the upper case there.
So this is another way to use it.
And again, a lot of people find that awkward,
nevertheless, that will work here, double click,
who gets the bonus?
Everybody except someone who's hourly.
So every time we see a blank here in column I
it's because that person is hourly.
So that's a different construction as well.
So at different times, be alert,
not only to the idea of these,
but the fact that you sometimes need to use these.
And again, not as a little bit unusual,
but the other two "and" and "or",
widely used when we need to test for multiple conditions
within an "if" function.
(soft upbeat music)