Join Dennis Taylor for an in-depth discussion in this video Using IF functions and relational operators, part of Excel 2016 Essential Training.
- One of Excel's most powerful functions is called the IF function. It's almost programming-like in nature, and it opens the door to a different way of thinking. The IF function is a conditional kind of function. It allows us to come up with multiple answers, based on a condition. In column G, in this worksheet called IF Function, we're in the workbook 10, IF VLOOKUP COUNTIF, in cell G4, we're going to apply a shipping charge. If the subtotal in column F is over 1,500, we want that shipping charge to be forgotten, in other words, no shipping charge.
Otherwise, it's going to be 2%. So we want a formula in G4 that checks the data in F4. If it's greater than 1,500, the shipping charge is zero. Otherwise, it's 2%. In column H, we've got a total that's adding these two columns, even though, for the moment, column G is empty. So we see why we're getting the same number there. Now, column G does not really have to be made wider, but in order to see the function, I'm going to make column G wider, and also, at the bottom of the screen, lower right hand corner, click the plus on the slider bar, zoom in a bit.
There we go. I'm going to be using the IF function here, and then down the entire column. The IF function begins with =if, a condition. And many times we're simply comparing two cells, or a cell with a formula, or a cell with a value. In this case, it's a cell with a value. We're simply about to say, if this cell F4 is greater than, this is the greater than symbol. On most keyboards, that's two to the right of the letter M. It's greater than 1,500. Now, you want to be clear when you do this.
What if it's 1,500 exactly? If you want to make sure that 1,500 exactly doesn't get the charge, then you want to put an equal sign in here as well, and we read this as, if F4 is greater than or equal to 1,500. Notice that below this we see the highlighted pop-up tip here that says logical_test. As soon as I put the comma at the end here, notice how the focus shifts over to value_if_true. We're now going to put in two different results. When this is true, our answer is zero, when it's not true, in other words if it's under 1,500, we put in 2%, and in this case, we're going to be multiplying it by the subtotal, asterisk subtotal, the F4 amount.
A right parenthesis doesn't need to be typed here. Excel will take care of that for us. I'll press Control + Enter. There we are. It's $29. And by double-clicking the lower right hand corner, this gets carried down to the bottom. And you can see that the totals on the right have been adjusted. That's adding the two numbers. Once again, let's look at the formula here, and I'll make the column even wider so you can see it more clearly. Double-click. There it is. Many times, the IF function is simple and straightforward, the way it is here. Now, the company might change its mind and say something like this.
We're going to have a lower percent here, if the amount is over 500, but under 1,500. The logic of the IF function actually works left to right. When this is true, the answer is zero, and the logic stops and that's it. But what if the number's 600 or 300 or something like that? We can also put in another IF, and that raises the complexity of this, although, after awhile, it doesn't appear to be too complex if you work with these frequently. What we're about to say is, if the number isn't 1,500 or more, let's put in another IF to see if that subtotal is greater than or equal to 500.
And if it is, comma, we'll put in 1%*F4, comma, and if it's not, we'll put in 2%*F4, In other words, if it's below 500, it's going to be 2%, so anything above 500. Now, what we're keeping an eye on, perhaps, is what we see in row five. We see a number down there, just below it. Now, this is an IF inside of an IF. It raises the complexity level, as I suggested, and ultimately you can put in lots of nested IFs, but that's not really a goal and shouldn't be.
I need another right parenthesis on the right hand side, and as I press Enter here, we haven't copied this down just yet, but I'll copy down just one cell here to see the difference. This is above 500, so instead of the 13.20, we're going to see a number roughly half of that. And there it is, 6.60. So it's a more complex use of IF. I'll make the column even wider. Double-click right here to expose it again. So, once again, and in English, we're saying the following: if the number to the left here, the subtotal, is greater than 1,500, we're going to put in a shipping charge, zero, but if it's not, in other words, if it's below 1,500, we're going to be checking to see if it's 500 or more.
If that's the case, the shipping charge will be 1% of the subtotal. If it's below 500, it's going to be 2%, and we complete that. And we would eventually copy this down the column, and we can do that pretty fast by double-clicking. But we've got all of our formulas in place. And then we can simply click the boundary between columns G and H. This is a simple example, although the second portion of it got a little bit more complex. The IF function, it's a powerful tool. It allows us to come up with multiple answers.
- Working with the Excel interface
- Entering data
- Creating formulas and functions
- Formatting rows, columns, cells, and data
- Working with alignment and text wrap
- Adjusting rows and columns
- Finding and replacing data
- Printing and sharing worksheets
- Creating charts and PivotTables
- Inserting and deleting sheets
- Using power functions such as IF and VLOOKUP
- Password-protecting worksheets and workbooks
- Sorting data
- Analyzing data with Goal Seek and Solver
- Creating and running macros