Join Dennis Taylor for an in-depth discussion in this video Explore IF logical tests and use relational operators, part of Excel: Advanced Formulas and Functions.
- [Instructor] One of Excel's most powerful functions if the if function. At times, it can be used in almost a programming-like way. On this worksheet called Simple IF, we're about to use it in a very simple, straightforward way. We're in our chapter two, if function file. In cell I2, we're going to provide a bonus for those people who have a job rating of four or a five. What we're actually looking for here is a way to provide one of two different answers. In more complex scenarios, the if function can be used, and the function can be quite long, to provide multiple answers. Let's start with a simple idea. If the job rating is four or five, the bonus is 3,000 dollars. Otherwise, nothing. Equal if. The if function in its basic form has three different parts to it. These are often called arguments. Some people call them parameters. We begin with a logical test. A logical test could be comparing the value of two different cells, and we could compare a cell with a value. We're about to do that, but in other situations, you might compare a cell's value with a formula. A lot of variations there. You could also test a cell to see if it contains a certain text entry. The example here, we're checking cell H2 to see if it's four or five. We can do this in two ways. It's greater than three, that's one way. Another way is to follow the greater than symbol with the equal sign, and then put in four. And, the way we read this is, if H2 is greater than or equal to four. I'll use the slightly shorter version here. If it's greater than three, comma, the end of the test here. Now, we will provide two answers. These answers can be numbers, they can be text, they can be formulas, they can be other cell references. When this is true, this company's decided to provide bonuses of 3,000 dollars, comma, if not, zero. Now, we can display the zero if we wish or we could put in double quote, double quote, which means display a blank or nothing. We could, in some scenarios, envision text to put between the double quotes. Let's say, no bonus or something like that. Let's say, for the moment here, we simply want to display nothing. When you're using a function by itself, you don't have to type in the final parenthesis. Excel will do that for us, so if we were to press enter right now, we expect, in this case, to see 3,000, because the job rating is five, and that happens. Let's test this out a little bit. Drag it down into a few more cells here. Make sure it's working okay. It appears to be working. We see the 3,000 for here, for example, because the job rating is four. And, the other examples as well. Looks pretty good. We'll take that last entry and copy it all the down the column. This is about 700 rows or so, and a quick way to copy data down a column, whether it's pure data, text, numbers, in this case, formula. Double-click the fill handle on the lower right-hand corner. This copies it to the bottom. If you're not sure how far it got copied, press control period. It'll take us down to the bottom, and there we are. Row 742. If you're not sure, scroll a little bit more. Seems to be working okay. Press control period again. Now, it could be that this is going to make more sense if instead of having 3,000 here, we're going to experiment with some different entries. Look what happens when I click column I. The status bar, bottom of the screen, tells us the total, a million and thirty-two thousand. That might well be within the limits of what this company's expecting, but maybe they want it to be closer to a million. So, instead of experimenting over and over and over here, we could, over in cell K2, for example, put in a number. Maybe 2900 will be close enough, we'll see. Let's re-work the formulas here. I'll double-click on cell I2, and instead of 3,000, let's put in the reference to cell K2. We do want to copy this down the column, so K2 needs to be an absolute address. I'll press the function key F4. Another option would be to give that cell a range name. This is going to work here. I'll press control enter, then double-click to copy this down the column. A new total, we see it down in the status bar, 997,000. By the way, if you do not see a number in the status bar, right-click within the status bar at the bottom of the screen. Here are the six different potential statistical measures you might want to have present. These are triggered anytime you highlight two or more cells with values. So, we've seen an example of the if function, using a very straightforward way here. We've got a condition. When the condition is true, we've got one answer. Earlier, it was just a number. Now, it's a cell reference. We provide another answer, in this case, a text entry a blank, and the condition is not met. So, a simple, easy to use example of the if function.
- Displaying and highlighting formulas
- Debugging formulas
- Creating 3D formulas
- Creating nested IF functions
- Exploring VLOOKUP, MATCH, and INDEX
- Tabulating data with COUNTIFS, SUMIFS, and AVERAGEIFS
- Finding values with statistical functions
- Adjusting results with rounding functions
- Converting values between measuring systems
- Calculating dates
- Returning reference data
- Manipulating text
- Extracting information