馃攳
Excel IF Formula: Simple to Advanced (multiple criteria, nested IF, AND, OR functions) - YouTube
Channel: Leila Gharani
[0]
Today's video is about Excel's IF function.
[4]
How can we use it alone?
[6]
How do we know if we need more than one IF?
[9]
How can we combine it with other logical functions
such as AND or OR functions?
[21]
Whenever you need a formula that's based on
a condition, such as you need to mark or flag
[27]
entities separately if their value's above
200 in revenue, an IF can do a good job here.
[35]
The way you know if you need a nested if,
that means you need to use another IF inside
[41]
an IF, is when you use buts in your sentences.
[47]
Let's say we need to mark entities if their
value is above 200, but if their value is
[52]
below 50, we need to follow up on them.
[55]
Let's take a look at some examples.
[57]
In this example, I have a list of apps, and
I have the revenue that's associated with
[62]
each app.
[64]
I've been asked to solve for these.
[66]
For the first case, I need to mark apps with
the word Good if the revenue is above 15,000.
[73]
We're going to start off with IF.
[77]
The first argument is our logical test.
[79]
What is our logical test in this case?
[83]
Is this value, right?
[84]
That's our revenue, and if this is greater
than 15,000 ... I can either type it in like
[92]
this or if I have these values in other cells,
I can reference those cells.
[101]
Obviously you're going to be more flexible
if you go with a cell referencing route, because
[105]
it could be that I change the threshold next
month to 20,000.
[110]
It's much more transparent and easier for
me to manage if I just have to go to one cell
[115]
and change that number, instead of finding
the formula, changing it in that formula,
[120]
and then dragging it down and making sure
that it applies to everything else.
[123]
I'm not going to type it in here.
[126]
I'm actually going to reference this cell,
but when you do the cell referencing route,
[131]
you have to keep the fixing in mind, Right?
[135]
That I can do in one go with the F4 key.
[139]
That's basically my logical test.
[142]
Check is this value greater than this value.
[146]
If it is, then we go to our next argument.
[149]
What should it do?
[151]
Here I want to type in Good.
[154]
Again, I can type it in like this, but you
have to be careful, because if you're typing
[159]
text in a formula, you have to put it in quotation
marks.
[163]
In this case, I also want to do a cell reference.
[166]
So I'm going to reference this cell, and again
I'm going to fix it.
[170]
Okay, so that's what it should do if this
does happen, and if it doesn't happen, then
[178]
I want it to do nothing.
[179]
Nothing, in Excel, means you can put in a
double quotation mark.
[184]
Close the bracket.
[185]
Press Enter.
[187]
Now we're going to send this formula down
and just double check.
[192]
This is above 15,000, and these are above
15,000.
[195]
Okay, so it looks good.
[197]
Now let's go to the next one.
[201]
It's not just above 15,000, but we want to
mark the entities as Good if the revenue's
[207]
greater than 15,000 and less than 20,000.
[212]
Situations like this call for the AND function
inside the IF function.
[220]
AND allows us to do a logical test for more
than one thing.
[225]
In this case, we have two things to check
for.
[227]
We're still going to start off with our IF,
but right here, before we start typing in
[233]
our logical test, we are going to put the
AND function first, and type in all our tests
[240]
that should occur and should be true inside
the AND function, okay?
[246]
What's one of these logical tests?
[248]
It's the same one we had before.
[250]
Is this revenue value greater than this value?
[255]
I'm going to fix it.
[257]
The next argument is your second logical test.
[261]
That's, again, is this value, now we want
to say is it less than this value.
[268]
I'm going to fix it as well.
[270]
Now don't forget to close the bracket for
the logical test, for our AND condition here.
[278]
The next argument is what should it do if
both of these occur, if revenue is in between
[283]
15,000 and 20,000.
[286]
We said we want Good.
[288]
In this case, I'm going to type it in, otherwise,
we want nothing.
[293]
Close the bracket.
[294]
Press Enter.
[296]
Let's see if it filters it out correctly.
[300]
These are between, this is between, and that's
between.
[303]
These are too high.
[306]
Next challenge.
[307]
If revenue is greater than 15,000 and 20,000,
so this is similar to what we did before,
[314]
but now we have an additional condition.
[317]
If revenue is greater than or equal to 20,000,
we want them marked as Exceptional.
[324]
Rest is value.
[326]
By value, I mean just the value that's in
the cell.
[329]
Basically, we're going to have a mixed column,
some text and some numbers in here.
[334]
How do we deal with these conditions?
[337]
The first part is exactly like we did before.
[341]
I'm actually going to copy this.
[343]
Press Escape to leave, and paste it in here.
[347]
Let's just bring in my cell references up
here.
[351]
Okay, because if both of these conditions
occurs, then I want Good.
[357]
Otherwise, do I want nothing now?
[359]
No, because I'm not done with the formula.
[362]
I need to test for another condition.
[365]
If this doesn't happen, I still now need to
go and test is this app an Exceptional app
[372]
in terms of revenue.
[375]
Exactly in the value if false argument, that's
where you need to put your second IF condition.
[382]
What is our logical test?
[385]
This number, now is it greater than or equal
to this number.
[391]
I'm going to fix this.
[394]
Then what do we want?
[395]
We want it to write Exceptional, otherwise
... Otherwise means if none of these occurred
[404]
until now, what should it do.
[406]
We said we want the value.
[408]
I'm just going to do a cell reference here.
[411]
Now I have two IFs, so I need two brackets.
[417]
Let's just send this down.
[419]
Now we have the Good, we have the Exceptional,
and for everything else, we have the number.
[427]
That's how you can use nested IFs in your
formulas.
[432]
You're not restricted to two IFs.
[434]
You can obviously put another IF here if the
value is below another threshold, then do
[441]
this, otherwise put the value or put nothing.
[444]
It's just that the more nested IFs you have,
the more difficult it's going to get to understand
[449]
the formula.
[451]
One thing to keep in mind is that Excel does
leave the formula the moment it comes across
[457]
a true condition.
[458]
The moment this is true, it puts that Good
and it leaves the formula.
[462]
It doesn't go and evaluate all your other
if conditions.
[466]
That's something to keep in mind when you're
writing these more complex formulas.
[472]
Let's look at another case.
[474]
If revenue is greater than or equal to 20,000,
or it's less than or equal to 15,000, then
[481]
we want to type in Flag.
[484]
Basically anything in between, we're going
to leave alone.
[488]
How do we write this?
[490]
I'm going to start with the IF.
[492]
Another logical test that we can use is the
OR function.
[498]
An OR checks for if either of these conditions
apply.
[503]
The logical test one is this one greater than
or equal to this number.
[510]
We're going to fix it.
[513]
What is logical test two?
[516]
Is this number less than or equal to this
number?
[520]
We're going to fix it.
[523]
Don't forget to close your OR condition before
you leave.
[526]
That's something I sometimes forget.
[528]
I continue going, and then I realize, "Oh,
I forgot to close that condition."
[533]
Next one, what should it do if either of these
occur.
[535]
Well, we just want to type in the word Flag
there, otherwise, we're going to leave them
[541]
alone and put nothing.
[543]
Okay, so it flagged the first one.
[546]
Let's see if that's correct.
[549]
Is that less than?
[550]
Yes, because it's not in between these.
[553]
Flag these, these and these.
[555]
Okay?
[556]
That looks good.
[558]
Now let's take a look at another case where
we're going to use bigger formulas inside
[563]
our logical test argument.
[566]
We have budget values, and we want to show
the percentage difference, basically show
[573]
the deviation from actual to budget if that
deviation is a bigger deviation, that's plus
[580]
or minus 10%.
[582]
Whenever you come across cases where you have
formulas inside your IF function, it's easier
[587]
to start with the core formula first.
[590]
The core formula in this case is my deviation.
[594]
I'm just going to calculate that actual divided
by budget minus one.
[600]
Let's just drag this down and see what we
get.
[604]
The aim is not to put anything for these ones
that are between plus or minus 10%, so only
[612]
put in the bigger deviations in here, which
actually is these four numbers.
[618]
Okay, so how do we do that?
[621]
Let's start off with our IF.
[623]
That's a part of my logical test, right?
[625]
I want to evaluate the answer of this formula.
[629]
What do I need to put in here?
[631]
How do I handle that?
[632]
I need the OR function, right?
[635]
The OR always comes before.
[638]
Right after the IF, I'm going to type in the
OR.
[641]
My logical test one is to check the result
of this formula, and see is it bigger than
[647]
10%.
[649]
That's the first logical test.
[651]
The second one is take a look at the same
formula and see is it less than minus 10%.
[659]
Okay, we're going to close the bracket for
the OR condition.
[664]
What should it do if it's true?
[666]
It should give me back the deviation.
[669]
I'm going to paste that formula in there.
[671]
Otherwise, it should leave it alone and do
nothing.
[675]
I'm going to close the bracket, and I should
just get these four numbers.
[683]
In the last example here, I just wanted to
show you that you can also use symbols as
[688]
your result.
[690]
Let's say for the positive deviations, I wanted
an up arrow, and for the negative deviations,
[696]
or the deviation in this case, I wanted a
down arrow.
[700]
First step is to bring your symbols in your
Excel sheet.
[705]
I'm going to do that by going to Insert, Symbols.
[709]
The symbols I use most often are under Arial,
Geometric Shapes.
[712]
You can see them actually here.
[716]
Just find the ones that you like, and click
on them, press Insert.
[721]
That's the up one.
[723]
I want the down one.
[725]
I'm going to press Insert right there, and
then close.
[728]
I can use them as text inside formulas by
putting them in quotation marks, but I actually
[734]
want to do cell references to them, so if
I decide to change the symbol to something
[738]
else for another type of report, all I have
to do is replace that symbol in the cell.
[744]
So I'm going to put them in two separate cells.
[747]
Let's Control-X.
[749]
Cut this one out, and put it right here.
[753]
Can I use the same formula for here, and just
replace this with the symbol?
[759]
I can't right?
[761]
Why?
[762]
Because I'm using two different symbols, so
I need to split them up.
[766]
If I was using the same symbol, I can, but
I'm not in this case.
[772]
Let's just write this one from scratch.
[775]
We know our logical test by now, so that's
this divided by this minus one.
[783]
Let's do the positive one first.
[785]
If this is greater than 10%, then we want
the symbol, which is this one, and press F4
[793]
to fix it.
[795]
Otherwise, what do I need right here?
[798]
Can I just put the other symbol?
[800]
No.
[801]
If I wanted everything else that wasn't above
10% to show this symbol, than yes, but I don't.
[807]
I just want the ones that are below minus
10% to show the other symbol, so I do need
[812]
an IF here, and my logical test is the same,
so I'm going to copy this and paste it in
[820]
here, is less than now it's minus 10%, then
this symbol.
[827]
Let's fix it.
[829]
Otherwise nothing.
[830]
Close, close, because I have two IF conditions.
[835]
Let's see what we get.
[836]
Okay?
[837]
That looks good.
[839]
Now what you can obviously do to make this
simpler is if you calculate this deviation
[845]
in a separate column, and then just reference
that cell.
[848]
That way you don't have to calculate it inside
your formula all the time, but I just wanted
[853]
to show you that it is common to have formulas,
and much bigger and more complex formulas,
[861]
inside your IF function.
[863]
Depending on the outcome of that formula,
it decides which way to go.
[869]
Okay, so in this example, we saw different
uses of the IF function.
[874]
We took a look at a simple version, the version
together with AND and OR conditions.
[880]
We also took a look at nested IFs, and how
to use slightly bigger formulas inside your
[886]
IF function, and even how you can use symbols
in your formulas.
[892]
One question that can come up is could you
color the up arrows in a different color than
[896]
your down arrows?
[897]
Yes, you can.
[899]
You can do it in different ways.
[900]
You can either use conditional formatting,
or you can also use custom formatting.
[905]
I have different videos on these, so I'm going
to share the links to those videos in the
[911]
descriptions.
[912]
If you like this video, don't forget to give
it a thumbs up.
[915]
For more of these videos, don't forget to
subscribe to this channel, so that you can
[920]
get updates when new videos come out.
Most Recent Videos:
You can go back to the homepage right here: Homepage