From the course: Excel 2016: Advanced Formulas and Functions

IF logical tests

From the course: Excel 2016: Advanced Formulas and Functions

Start my 1-month free trial

IF logical tests

“

- One of Excel's most powerful functions is the IF function. It has an almost programming-like feature about it, and it's generally easy to use, although at times it can get quite complex. In this worksheet called Simple IF, we're about to create bonuses here for people who have certain job ratings. This particular company's decided that those who have job ratings of four or five are going to get a bonus of $3,000. Now, the column doesn't have to be wider. I am going to zoom in and make this larger and clearer on the screen. The IF function in its simplest form is direct and easy to work with. I will make column I wider. We're about to put in the IF function down column I. In English, we're saying the following. If your job rating is four or five, you will get a bonus of $3,000. Otherwise, nothing. Equal if. The IF function in its basic form has three arguments to it. Parameters some people call them. We begin with a logical test. We can be comparing two different cells, a cell with a formula, a cell with text, a lot of different comparison types here. We're simply going to be pointing to cell H2. You can click it or type it. Is that greater than three? That's a test. Now, we could phrase this a different way. All the job ratings are whole numbers. So by inference, what are we saying here? If your job rating is four or five. If we put in the equal sign here and then a four, we're saying essentially the same thing here. If your job rating is greater than or equal to four, that's how we read that construction. Don't reverse the symbols, by the way. It will not work. Greater than or equal to four or greater than three. Either one will mean the same in this context. I'll use the slightly shorter version by putting in the three. Comma. Now we're about to provide two different answers. When this is true, the answer's going to be 3,000. The answer can be a pure number, it can be a space, it can be a blank, it can be a calculation, it can be an external cell. It's simply 3,000 right here. And, similarly, the value when the test is false can also be text, a number. If we want zero in here, fine, we put zero. If we'd like to put in a blank, it's going to be double quote, double quote. Simply display a blank, and we don't have to type the right parenthesis. In some situations, this answer, this 3,000 for when the test is true, could be a reference to an external cell. Let's keep it this way at first. I'll press control, enter so the active cell does not move as I complete the entry. We're expecting to see a blank here because the job rating is one. That's what we see. I'll double-click the lower right-hand corner, and you can see what's happening below. Only when the job rating is four or five are we seeing this answer of 3,000 and pressing F2 to expose the formula. Simple, straightforward. It's either true or it's not, and we've got two different answers for the situations there. Now, if we click column I or have those cells selected, in the status bar at the bottom of the screen, we see the total impact that's over a million dollars. Now, if the organization is planning these bonuses, has planned on all along, providing about a million dollars worth of bonus numbers, well, they're pretty close right there. In another scenario, another kind of situation, we could use this cell as a holding place for a number like 3,000, and then maybe we'll experiment with that a little bit. So going back to our function over here to the left, and, again, just barely see that for the moment. That's fine. Go back here, and instead of this being 3,000, I'm going to let it refer to cell J2. If I do want to copy that down the column, that needs to be an absolute reference, so I'll press R4 to make J2 become an absolute reference and then copy it down the column. Now, the totals are looking the same, 1,032. If we say, "Well, what if we give everybody $2,800?" what's that total going to be? I'll change that to be 2,800, and if we click column I, our total at the bottom is a little under a million dollars. Possibly, you could use another feature called Goal Seek that would make this number be exact if we want these to truly add up to one million exactly, but that's a different topic. The IF function here in its simplest form simply states a condition. Here's the answer for when it's true. Here's the answer for when it's not true. And many, many times the IF function is that simple, that straightforward, and if this is all we wanted to do with the column here, we'd simply double-click the boundary between column I and J. The column needs to be only as wide as the result.

Contents