Today's video is about Excel's IF function. How can we use it alone? How do we know if we need more than one IF? How can we combine it with other logical functions
such as AND or OR functions? Whenever you need a formula that's based on
a condition, such as you need to mark or flag entities separately if their value's above
200 in revenue, an IF can do a good job here. The way you know if you need a nested if,
that means you need to use another IF inside an IF, is when you use buts in your sentences. Let's say we need to mark entities if their
value is above 200, but if their value is below 50, we need to follow up on them. Let's take a look at some examples. In this example, I have a list of apps, and
I have the revenue that's associated with each app. I've been asked to solve for these. For the first case, I need to mark apps with
the word Good if the revenue is above 15,000. We're going to start off with IF. The first argument is our logical test. What is our logical test in this case? Is this value, right? That's our revenue, and if this is greater
than 15,000 ... I can either type it in like this or if I have these values in other cells,
I can reference those cells. Obviously you're going to be more flexible
if you go with a cell referencing route, because it could be that I change the threshold next
month to 20,000. It's much more transparent and easier for
me to manage if I just have to go to one cell and change that number, instead of finding
the formula, changing it in that formula, and then dragging it down and making sure
that it applies to everything else. I'm not going to type it in here. I'm actually going to reference this cell,
but when you do the cell referencing route, you have to keep the fixing in mind, Right? That I can do in one go with the F4 key. That's basically my logical test. Check is this value greater than this value. If it is, then we go to our next argument. What should it do? Here I want to type in Good. Again, I can type it in like this, but you
have to be careful, because if you're typing text in a formula, you have to put it in quotation
marks. In this case, I also want to do a cell reference. So I'm going to reference this cell, and again
I'm going to fix it. Okay, so that's what it should do if this
does happen, and if it doesn't happen, then I want it to do nothing. Nothing, in Excel, means you can put in a
double quotation mark. Close the bracket. Press Enter. Now we're going to send this formula down
and just double check. This is above 15,000, and these are above
15,000. Okay, so it looks good. Now let's go to the next one. It's not just above 15,000, but we want to
mark the entities as Good if the revenue's greater than 15,000 and less than 20,000. Situations like this call for the AND function
inside the IF function. AND allows us to do a logical test for more
than one thing. In this case, we have two things to check
for. We're still going to start off with our IF,
but right here, before we start typing in our logical test, we are going to put the
AND function first, and type in all our tests that should occur and should be true inside
the AND function, okay? What's one of these logical tests? It's the same one we had before. Is this revenue value greater than this value? I'm going to fix it. The next argument is your second logical test. That's, again, is this value, now we want
to say is it less than this value. I'm going to fix it as well. Now don't forget to close the bracket for
the logical test, for our AND condition here. The next argument is what should it do if
both of these occur, if revenue is in between 15,000 and 20,000. We said we want Good. In this case, I'm going to type it in, otherwise,
we want nothing. Close the bracket. Press Enter. Let's see if it filters it out correctly. These are between, this is between, and that's
between. These are too high. Next challenge. If revenue is greater than 15,000 and 20,000,
so this is similar to what we did before, but now we have an additional condition. If revenue is greater than or equal to 20,000,
we want them marked as Exceptional. Rest is value. By value, I mean just the value that's in
the cell. Basically, we're going to have a mixed column,
some text and some numbers in here. How do we deal with these conditions? The first part is exactly like we did before. I'm actually going to copy this. Press Escape to leave, and paste it in here. Let's just bring in my cell references up
here. Okay, because if both of these conditions
occurs, then I want Good. Otherwise, do I want nothing now? No, because I'm not done with the formula. I need to test for another condition. If this doesn't happen, I still now need to
go and test is this app an Exceptional app in terms of revenue. Exactly in the value if false argument, that's
where you need to put your second IF condition. What is our logical test? This number, now is it greater than or equal
to this number. I'm going to fix this. Then what do we want? We want it to write Exceptional, otherwise
... Otherwise means if none of these occurred until now, what should it do. We said we want the value. I'm just going to do a cell reference here. Now I have two IFs, so I need two brackets. Let's just send this down. Now we have the Good, we have the Exceptional,
and for everything else, we have the number. That's how you can use nested IFs in your
formulas. You're not restricted to two IFs. You can obviously put another IF here if the
value is below another threshold, then do this, otherwise put the value or put nothing. It's just that the more nested IFs you have,
the more difficult it's going to get to understand the formula. One thing to keep in mind is that Excel does
leave the formula the moment it comes across a true condition. The moment this is true, it puts that Good
and it leaves the formula. It doesn't go and evaluate all your other
if conditions. That's something to keep in mind when you're
writing these more complex formulas. Let's look at another case. If revenue is greater than or equal to 20,000,
or it's less than or equal to 15,000, then we want to type in Flag. Basically anything in between, we're going
to leave alone. How do we write this? I'm going to start with the IF. Another logical test that we can use is the
OR function. An OR checks for if either of these conditions
apply. The logical test one is this one greater than
or equal to this number. We're going to fix it. What is logical test two? Is this number less than or equal to this
number? We're going to fix it. Don't forget to close your OR condition before
you leave. That's something I sometimes forget. I continue going, and then I realize, "Oh,
I forgot to close that condition." Next one, what should it do if either of these
occur. Well, we just want to type in the word Flag
there, otherwise, we're going to leave them alone and put nothing. Okay, so it flagged the first one. Let's see if that's correct. Is that less than? Yes, because it's not in between these. Flag these, these and these. Okay? That looks good. Now let's take a look at another case where
we're going to use bigger formulas inside our logical test argument. We have budget values, and we want to show
the percentage difference, basically show the deviation from actual to budget if that
deviation is a bigger deviation, that's plus or minus 10%. Whenever you come across cases where you have
formulas inside your IF function, it's easier to start with the core formula first. The core formula in this case is my deviation. I'm just going to calculate that actual divided
by budget minus one. Let's just drag this down and see what we
get. The aim is not to put anything for these ones
that are between plus or minus 10%, so only put in the bigger deviations in here, which
actually is these four numbers. Okay, so how do we do that? Let's start off with our IF. That's a part of my logical test, right? I want to evaluate the answer of this formula. What do I need to put in here? How do I handle that? I need the OR function, right? The OR always comes before. Right after the IF, I'm going to type in the
OR. My logical test one is to check the result
of this formula, and see is it bigger than 10%. That's the first logical test. The second one is take a look at the same
formula and see is it less than minus 10%. Okay, we're going to close the bracket for
the OR condition. What should it do if it's true? It should give me back the deviation. I'm going to paste that formula in there. Otherwise, it should leave it alone and do
nothing. I'm going to close the bracket, and I should
just get these four numbers. In the last example here, I just wanted to
show you that you can also use symbols as your result. Let's say for the positive deviations, I wanted
an up arrow, and for the negative deviations, or the deviation in this case, I wanted a
down arrow. First step is to bring your symbols in your
Excel sheet. I'm going to do that by going to Insert, Symbols. The symbols I use most often are under Arial,
Geometric Shapes. You can see them actually here. Just find the ones that you like, and click
on them, press Insert. That's the up one. I want the down one. I'm going to press Insert right there, and
then close. I can use them as text inside formulas by
putting them in quotation marks, but I actually want to do cell references to them, so if
I decide to change the symbol to something else for another type of report, all I have
to do is replace that symbol in the cell. So I'm going to put them in two separate cells. Let's Control-X. Cut this one out, and put it right here. Can I use the same formula for here, and just
replace this with the symbol? I can't right? Why? Because I'm using two different symbols, so
I need to split them up. If I was using the same symbol, I can, but
I'm not in this case. Let's just write this one from scratch. We know our logical test by now, so that's
this divided by this minus one. Let's do the positive one first. If this is greater than 10%, then we want
the symbol, which is this one, and press F4 to fix it. Otherwise, what do I need right here? Can I just put the other symbol? No. If I wanted everything else that wasn't above
10% to show this symbol, than yes, but I don't. I just want the ones that are below minus
10% to show the other symbol, so I do need an IF here, and my logical test is the same,
so I'm going to copy this and paste it in here, is less than now it's minus 10%, then
this symbol. Let's fix it. Otherwise nothing. Close, close, because I have two IF conditions. Let's see what we get. Okay? That looks good. Now what you can obviously do to make this
simpler is if you calculate this deviation in a separate column, and then just reference
that cell. That way you don't have to calculate it inside
your formula all the time, but I just wanted to show you that it is common to have formulas,
and much bigger and more complex formulas, inside your IF function. Depending on the outcome of that formula,
it decides which way to go. Okay, so in this example, we saw different
uses of the IF function. We took a look at a simple version, the version
together with AND and OR conditions. We also took a look at nested IFs, and how
to use slightly bigger formulas inside your IF function, and even how you can use symbols
in your formulas. One question that can come up is could you
color the up arrows in a different color than your down arrows? Yes, you can. You can do it in different ways. You can either use conditional formatting,
or you can also use custom formatting. I have different videos on these, so I'm going
to share the links to those videos in the descriptions. If you like this video, don't forget to give
it a thumbs up. For more of these videos, don't forget to
subscribe to this channel, so that you can get updates when new videos come out.
