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..
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..
Posts Related:
- How To Design Responsive Navbar Using HTML, CSS, & JavaScript - Part 2 BlogSpot
- How To Create A Discussion Forum Website (WordPress)
- Excel Today - Named Ranges in Excel
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..
