Use the IF function to set up logical tests (e.g. comparing two cells, comparing a cell with a value, a cell with a formula, and so on) and provide two answers - one for when the condition is true, one for when it's false. Answers can be values or text entries or can be references to other cells.
- [Voiceover] One of Excel's most powerful functions is the If function. It can be simple and straightforward. It can be quite complex and involved. We're looking at a worksheet called Simple If, and we're looking at a list here of people by department, building, compensation, status and so on. We're about to give bonuses based on the company decision that anyone who has job rating of four or five is going to get a $3,000 bonus. Otherwise, nothing. So in English we could be saying if your job rating is four or a five, we're going to put in the answer, if not we'll put in zero or blank.
So, I'm going to zoom in lower right hand corner. I'll simply click the plus button so we can see this a bit larger. Scroll a bit to the right. For the moment, we only need to see column I and column J. In its simplest form, the If function simply is equal if it has three arguements to it, sometimes referred to as parameters, a logical test. A logical test if you've never seen the term before, you wouldn't know where to begin, it means we either are going to be comparing two cells with one another or a cell with a value, a cell with a text entry, a cell with a formula, and all those various combinations we might be coming up with.
So all we want to test here is to see if that cell J2, so you can click it or type it, is greater than three. This is one way to make the test. Another way if we put in the equal sign as well and put in a four, the way we read this is, J2 is greater than or equal to four. Now within this context where all the job ratings are whole numbers, it makes no difference whether we say greater than or equal to four or simply greater than three. It means the same thing. Now, notice that the popup below here that's helping us along so to speak.
We're on the logical test portion of this. As soon as I put in the comma, notice how the bold print will shift over to value if true. The If function will allow us to provide two different answers. When this test it true, we've got an answer, and the answer can be a cell reference, it can be a value, it could be a text string, it could be a calculation, any number of different things. If it's simply going to be a value here, say 3,000, put in that number. And similarly, as I put in a comma, the value when the condition is false, zero certainly makes sense much of the time.
If you want to put in blank, use double quote, double quote. Sometimes referred to as the null string. We don't really have to type the right parenthisis here since we have only a single set. I could press Return, but I'm going to press CTRL Return because I don't want the active cell to move downwards. So I'll press CTRL Return, and then double click, copy this down the column. And immediately we can see the results. Where do we not see the 3000? When the job rating is three or two or one.
I can press CTRL U here to display the function. It looks like that. So a simple straightforward, no real surprises there as we see the answer. Now, a side benefit of this too which could be helpful at times. While all these cells are still highlighted, we see in the status bar at the bottom of the screen that the total of these bonuses, is slightly over a million. So it's quite possible that whoever set this up was thinking, well this should cost us about a million dollars, but you know, it's worth it, and we've got that money, so...
That's a number to keep an eye on. Another thought here is, if you want to experiment with that, we could have an external reference. So what if over here, for example, in this cell to the right, I put that value 3000 and then in the formula here, and I'll be recopying it, instead of this saying 3000 right here, I'll click cell K2. Now if I'm going to be copying this down the column, I'll want that to be an absolute address, so I'll press CMD T, that makes this an absolute address.
I'll complete the entry with CTRL Return, copy this down the column all the way. We still have the same total, but look what happens now if I jump to cell K2 and say, well, what would it cost us if this were 2800? And as soon as I press enter, all those formula results will be different, and as I click column I, look at the bottom of the screen, 966,000. So you can imagine with certain scenarios here experimenting with that number to come up with different answers, could do some guessing.
There's even a feature called Goal Seek that you might want to explore. The company's come into some more money, it's saying well, we could afford bonuses of 3500. Let's try this, and what's our total now if we click column I? 1,207,000. So you could begin to see how this could work, you know, one of two major ways. Either we put in the pure value here, change it and recopy if necessary or simply make a refer to an external reference as we saw here. But, in the simple example here, and many, many times the If function is this straightforward.
We've got some kind of a logical test, some kind of a comparison, and we provide two answers. One for when it's true. One for when it's not true. If we want zero here, we'll put the zero in. We don't need the double quote in that case. Complete that, recopy. Now the zero might not display. It depends upon the format that we're using. If you're using comma format like right here, you don't see this. If you use certain other kinds of formats, for example if I were to right click column I and choose Format Cells.
If we use, for example, in the number tab here, the number format, this is likely to display the entries here with true zeros. And you'll decide whether you like that or not. You could decrease the display of the decimal this way. So there's some variations on how this gets displayed. But the main thrust here is the idea behind the If Function. Many times, just simple, straightforward. And one other variation here. Earlier we saw two double quotes. We could put in double quote here, put in, now this probably wouldn't be the nicest thing to do but it does accentuate the idea that some people wouldn't be getting the bonus.
This is what happens when the job rating is one, two or three. As I recopy it. And it appears on the left side because it's text. Ultimately the column only has to be as wide as the entry, so if that's the way we wanted to keep it, we could do this. So that's another approach too. Just different ways of using this If function in a very simple, straightforward way.
- Understanding how the hierarchy of operators affects formula results
- Knowing when to use absolute vs. relative references
- Using Formula Builder for unfamiliar functions
- Displaying a worksheet's formulas and highlighting formula cells
- Converting formulas to values
- Creating 3D formulas to gather data from multiple sheets
- Creating and expanding nested IF functions; using AND, OR, and NOT with IF
- Looking up information with VLOOKUP, HLOOKUP, MATCH, and INDEX
- Analyzing data with the statistical functions: MEDIAN, MODE, etc.
- Using the power functions: COUNTIF, SUMIF, COUNTIFS, AVERAGEIFS, and more
- Calculating financial data such as payments
- Performing basic math
- Calculating dates and times
- Editing text with functions
- Using array formulas and functions
- Referencing data in other cells and files
- Extracting information from Excel worksheets