Using the IF Function In Numbers

Learn how to use the IF function to perform calculations in Numbers. IF is an important function to learn if you want to graduate to creating more advanced spreadsheets. You can use it to test a value in one cell and return two different values depending on the results of that test. Also learn related functions like SUMIF and COUNTIF.

You can also watch this video at YouTube (but with ads).

Video Transcript: Hi, this is Gary with MacMost.com. Let me show you how you can use the IF function in Numbers.
I'm using Numbers version 3 here. Let's take a look at IF statements. We have a blank spreadsheet here with a blank table in it. Let's enter some data in.
We have a list of numbers. Let's use an IF statement on these. So we can do = IF and we can see right away it brings up a function right there. We can look up IF in the functions on the right. See what parameters it takes. It says IF and there's an expression and then a True and a False. We can see examples of it.
So for instance, back to our formula, let's say IF B2 is greater than 4. Then we'll say yes; otherwise no. You can see the result there. If I copy and paste it here you can see the formula is going to update. This is going to look at B3, this is going to look at B4, etc. You see it will put no or yes into these depending upon the result. This is the simplest version of using the IF statement. Simply outputting something different for whether or not the condition is true or false.
But we can do more than just have an indicator here. So, delete all of this and let's enter in a second bunch of numbers here. Make them all twos here. Let's say that these are all amounts here so they will all be currency.
We can say, well, this is a purchase. For everything that you purchase that is less than say $5.00 you have to add a $2.00 shipping charge to it. So we can say that the total cost would be IF this number is less than 5 then return this number plus the number next to it; otherwise simply return this number. 
So you can see what I've done here. The condition is whether B2 is less than 5. If it is then let's get the value of both of these together. Otherwise just the value of just this one here. 
We can see that this gives me $5.00 because it should add the $2.00 shipping. This does not give me 7, it gives me 5, because I'm not adding the $2.00 shipping. So you can see that one use of the IF statement is to make calculations based on criteria.
So you can also nest IF statements, if you really want to. So for instance let's say you want to do IF this number is greater than 5 then, if it is, return the number. So we're just going to do B2. If not then we also want to check further and say IF that number is less than 2 then return that number and add a $10.00 surcharge there. Otherwise, if it is not, then only add a $5.00 surcharge. We have two closing parentheses there. So we are nesting one IF statement inside the other.
The first one is just B2 is greater than 5 then return B2. Otherwise, then we have the next IF statement. Otherwise IF B2 is less then 2 then return the number plus 10. Otherwise, and we forgot to add that there, otherwise plus 5.
So three different possible outcomes by nesting this. You can write this down on a piece of paper if you are not used to nesting this to see how it works. Then we get the answer here. So we can see that last one added that 10 to it right there whereas this one just adds a 5 to it. So we can see it works out. 
You can nest but if you have a lot of nesting, if you have any more than this, I would use the Lookup function instead. So you can look that up in the Functions to see how the Lookup function works.
So there are a few other variations on the IF statement. You have IF over here but you can also see there are also SUMIF returns the complete sum of a set of numbers. You also have COUNTIF which returns the count.
So, for instance, to use COUNTIF, I can go down here and say COUNTIF and this is the test range. Which would be that. The condition and this is going to be like greater than 3. You put that in quotes. You can see there here in the example how it is using quotes. It is going to return that there is 3 of them. You probably don't want that in dollars, we want that as a regular number. So we can go in here and say well what IF it is greater than 2. You can see that there is four of them that are greater than 2.

Using the IF Function In Numbers

Comments: 2 Responses to “Using the IF Function In Numbers”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information