Join Dennis Taylor for an in-depth discussion in this video Exploring IF logical tests and using relational operators, part of Excel 2011 for the Mac: Mastering Formulas and Functions.
- One of the most important functions in Xcel is the IF function. In some respects, it's like a threshold function. It opens the door to a more powerful use of Xcel, it has programming-like capabilities in the sense that it does allow us to come up with alternate answers depending upon circumstances. In this particular worksheet called simple IF, we're about to apply a bonus, or maybe lack of a bonus, for each person here based upon that person's job rating.
This particular organization has decided that if you have a job rating of four or five, those are the good ones, then you will get a bonus of $2,500. Otherwise, nothing, at least for the moment. The IF function allows us to essentially set up a condition. Is this job rating four or five, or is it not? And then provide two different answers. Ultimately, it can be more expensive, we can even have three, four, or five multiple answers. Let's start off with a very simple IF.
Recognize that the IF function can and is many, many times simple, straightforward. I'm going to zoom in on this. I've made column I wider than is truly necessary because as we create the formula here using the IF function, we want to be sure it's large and clear on the screen. So I'll zoom in a bit here. Equal IF. The IF function begins with what's called a logical test. Now without a previous example, you wouldn't know exactly what to do here, but a common way to use a logical test, or to set up a logical test, is to compare two different cells, or to compare a cell with an external value.
Sometimes you'll be comparing a cell with a formula or two different formulas. Lots of variations here. You could also test to see if there's a certain kind of text that exists in the cell. So in our particular example here, we want to check to see if cell J2, I'll simply click it here, is greater than three. Now if we want to say four or five, this is an indirect way of doing this. None of the job ratings is a decimal. Therefore, these are whole numbers.
So logic says that this means a four or a five. A different way to say this could be clearer to you, possibly, is to put in the greater than symbol followed by equal to and put in a four. The way we read this is if J2 is greater than or equal to four, and of course within the context of what we're seeing here, this is equivalent to saying greater than three. Anytime you use these two symbols together, they must be in this particular left to right order, the greater than symbol followed by equal to.
Then we put in a comma. Is that test true? In the current example, it's not true, but if it were true, we want to give an answer here. Now the answer can be a calculation, it can be a cell reference, it could be a text string, it can be a simple value, for example 2,500. To not put a comma after the two here, commas are critical in the IF functions, and they designate different break points about the arguments within an IF function, sometimes called parameters.
So that's the answer when the condition is true. Comma, how about another answer for when it's false? If you want to put in a zero here, that's fine. If you'd like to display nothing, you could put in double quote double quote, sometimes this is referred to as the null string. This will display a blank if the condition is not true. There will be situations where maybe you want to put in a word or a phrase there instead. That's fine too. So I'm simply going to press ctrl + enter here so I can rapidly copy this down the column.
What we're expecting to see here as I complete the first entry is blank since the test fails. In other words, the job rating is not greater than or equal to four. So control return, I'll double click the lower right hand corner. In some of the other cells, of course, where do we see the 2,500? Only when the job rating is four or five. Now by pressing ctrl + u, you can see the display here of the first formula, and it's very simple, very straightforward, and probably no real difficulties in logic here.
It's either true or it's false. We get one answer or another. Many, many times, as I said, the IF function is this simple, it's this straightforward. Just for a variation here, if I were to click between the double quotes here and were to say no, or no bonus, or if I were perhaps a little sarcastic, I might say sorry. I won't do that, but you can put in anything you want here if you wish to display text. Since all these are selected at once, I'll just press control return together and we'll see a different display here.
The only reason that's on the left side is because that's text and the numerics are on the right. Ultimately, if this is all we wanted to do, we would simply double click the boundary here because really we don't need to make the column wider, but I was doing it so we could easily see the formula in its entirety without hiding the other data. So a simple example of a standard IF function. Many, many times simple, straightforward, easy to use.
- Using absolute, relative, and mixed references
- Using the Formula Builder
- Converting formulas to values
- Creating 3D formulas to gather data from multiple sheets
- Creating and expanding the use of nested IF statements
- Looking up information with VLOOKUP, MATCH, and INDEX
- Using the power functions: COUNTIF, SUMIF, and AVERAGEIF
- Analyzing data with the statistical functions
- Calculating payment
- Performing basic math
- Determining dates and times
- Editing text with functions
- Analyzing data with arrays