Learn How To Use the IF Function In Numbers

Hi this is Gary with MacMost.com. Let me show you how to use the IF function in Numbers.

MacMost is brought to you thanks to some great supporters. Go to MacMost.com/patreon. There you can read more about the Patreon Campaign. Join us and get exclusive content.

So one of the keys to learning how to use spreadsheets is learning how to use the IF function. IF functions are very important and a lot of the questions I get on how to do something in Numbers usually results in an answer that has an IF function in it. Here are six examples to get a handle on how the IF function works. I'll make this Numbers file available on the website for you to download.

In the first example we have the simplest form of an IF function. We're just getting to test a single number. So we have this number here and we're going to test it. If it's greater than ten we're going to put the word Yes and if it's less than ten we're going to put the word No. So here's how the formula looks. It's just a basic IF statement there and it's testing for the value of this, A2, to be greater than, >,  10. So there are three parameters to an IF statement. The first one is the test. The second one is what to put in the cell if it's true. The third one is what if it's false. So if A2 is greater than ten then put the word Yes there. If it's not then put the word No there. So let's test it out. We've got 11 and it says Yes. If I were to change this to 9 you can see it changes to No. So it updates automatically doing that test and putting a new value in that cell.

The second example we're going to do something that is very common with IF statements. We're going to pass the value through one case and put a word there in another case. So we have, just like before, a number here and here is the formula for this second cell. We're doing the same test. IF A2 is greater than 10 and then we're going to put words just like before except instead of Yes we're going to say Too High if it's great than 10. But instead of No we're going to pass the value through. So put the value or A2 there. So you can see this in action here. It's 9, which is less than 10 so it passes the value through. Any value we  put that's less than 10 just pass it through. If we put it greater than 10 then it's going to Too High. It doesn't matter how much greater than 10 it is it's going to still show Too High.

Now you can do more than just testing a single value. You can actually perform a calculation. In this case we've got two numbers here. We're going to add them together and test the result. So here is the IF statement for this. So we're looking at A2 + A3 and testing to see if that is greater  than 10. Then we're going to put Too High if it is and if not we're going to pass the value through by using the same addition there, A2 + A3. Now this could be any type of calculation. You could do minus. You could do divide. You can use different functions and things like that inside of this. But we're just going to do a simple addition here. So 4 + 5 is 9 it's going to pass this through. If I were to change this to be 7 you could see it says Too High. If I were to go and change this one to be a large number you can see it says Too High. As long as the sum of these, 4+5 in this case, is less than 10 it's going to pass it through. Otherwise it will say Too High.

Now looking at formulas like this, this first parameter here is a test. The result of a test is always True or False. So anything you put in there has got to be something that results in true or false. You can test something that is greater than, is less than, is equal to and do any number of functions to test to get the result that is either true or false. The results are the true value here, the false value here. 

In this example we're going to simply look at the value of a cell that is set to True or False. The simplest way to do that in a spreadsheet is to use a checkbox. So I've taken this cell. I've change the cell format to Checkbox. But I could also just have it be a number and use zero or one for False or True. So in this case we'll do the checkbox and the formula is IF checked, so if true, then return the value A3 else return the value A4. So there are two numbers here and basically if this is true it's going to take this one and put it there. If this is false it's going to take this one and put it there. So you can see that happens now. If I change the value of the checkbox you can see the result there changes. 

Let's say you want to test more than one thing. Each of these four is just testing one single thing to see if something is greater than something else or something is true or false. But what if you want to test two things. So in this case I have two numbers. I want to test this first one to see if it's greater than 10 and the second if it's greater than 20. Put the word Yes there is both of these things are True. If this is greater than ten and this is greater than twenty. The way we do that is we use and AND function. An AND function inside the IF statement. So here's the IF statement. The first parameter is an AND function in here. The AND function has two parts to it. This part here testing  for this value to be greater than 10 and this part here testing for this value to be greater than 20. There's a comma in-between them. So you're testing those two things. You could actually a third thing there. You could do comma and then a third thing to test. It would test for the first thing, and the second thing, and the third thing. It will only give a True result for the entire AND function if all of these things are true. Then it will do Yes. Otherwise it will do No. So in this case 11 is greater than 10 and 21 is greater than 20. But if I were to change this to say 19 you could see that only one of the two things is true. So this is true, this is not true, so the AND function fails. You get a false result and then we have our No. If I change this to 21, Yes again. But if I change this to 9 then that result is false. Because this is false and this is true and combined they are false.

Now let's say you wanted to do OR instead. You just want to see if one of these things is true.  You can do that as well. All you need to do here is change AND to OR and it works the same way. It tests two things or more. It could be three, four, five things. In this case there's two things and it if anyone of them is true then the entire OR function is true and we get our result. So here they're both true so the thing is true. But even if only one of them is true then we still get the Yes. If both of them are false though then it's false and we get No. 

The last thing I want to show you as part of this introduction to IF statements is Nesting IF Statements. Suppose we want to do more than one test but we want different results depending on which test passes. So, for instance, we want to take this number and show one thing if it's less than ten and another thing if it's less than twenty, and something else if it's greater than twenty. Well here is an IF statement that does that. It's actually a nested IF statement. So the first IF statement tests to see if the number is less than 10. If it is it just simply returns this value and puts that there. If not then it's going to do another IF statement. That one is going to do a similar test but test to see if the value is less than 20 and then return this. Now if that's not true then it's going to return this here instead. So in other words this first one has failed so it goes into the second one. The second test has failed so then it gives you the result. I could replace this with another IF statement. A third nested IF statement there to continue to do tests. However if you really want to have more than two or three of these nested then you should probably graduate to using more complex functions like match or lookup. Here if we test this out you can see we get the result less than 10. If I do 11 and you can see the first IF statement fails. It goes to the second IF statement  and that succeeds so it's less than 20. If I change this to 21 now both of them fail and we end up with that result of 20 or greater that we see here in this function.

That's the basics for using IF statements. Once you master all these different ways of using IF statements you can create some pretty powerful spreadsheets.