Learn how to use if statements in formulas in iWork Numbers. You can combine these with checkboxes and pop-up menus to create results based on conditions. Useful for solving problems and bookkeeping.
Hi, this is Gary, with MacMost Now. On today’s episode let’s use if statements and checkboxes in iWork numbers. So let’s say I’ve got a list of prices here, maybe these are for items sold, and I want to have a total, multiplied by a sales tax amount, but only if I’ve checked off the box. Well, the way to add a checkbox in Numbers is to select the cell or cells that you want, bring up the inspector, and change the format of the cell to checkbox. This will put a checkbox in each one of these. Now to use a checkbox, what you want to do is create a formula that uses an if statement. So I’ll start by typing equals, and that will begin my formula, and then if, and then a parenthesis, now there’s three parts to an if statement; the first is the condition, the second is what you get if it’s true, the third is what you get if it’s false. So for condition, we’ll simply click on this checkbox here to the left, it’ll add cell C2 to it, and that’s the condition. If it’s true, that means it’s checked. I’ll do a comma, and then I’ll say if it is checked, I want to take this price and multiply it by a sales tax amount, so lets say 1.07, then comma, and if it’s not true, then I’m simply going to use cell B2, the plain cell by itself, not multiplied by anything as a result. And, I’ll put a right parenthesis there, and there’s my formula. I hit return, and you can see it puts 2.70 into there. I’m going to copy it, and paste it in all of these. Now what happens if I check one of these boxes? You can see the price there jumped because it’s now showing the first condition for the if statement rather than the second. Which means it multiplies it by 1.07. I can do that for all of these and take them away. Now lets say instead of having checkboxes, you want to have a condition determined by something else, so for instance lets take away the checkboxes, and uh, let me simply put a formula in here, equals, and we’ll put this, the column before, and use greater than four. So what’s going to happen is it’ll put true or false in there based on whether the price is greater than four dollars. So it’s kind of like we’re only charging sales tax here for items greater than four bucks. And if I paste it into all of these, you can see that this row and this row are true, because both those values are greater than four dollars. And then this true or false is then used in this if calculation here. You could also put something else in this column, say, uh, for instance if you’re going to charge sales tax for only certain types of items. So, we’ll do like, type. And then we’ll say grocery, and other, and we only want to charge, uh, sales tax for grocery items, so I’m just going to place a bunch in here like that, and then I’m going to change this formula here, instead of just saying C2, I’m going to actually erase that, and say if C2 is equal to, lets do other, for sales tax, and now see that sales tax is only applied if this column is equal to other. Now likewise we could change this column instead of having to manually type grocery and other, uh, we could change it to cell format, popup menu, and then give it items here, like for instance, uh, we could get rid of the default one two and three, and add grocery and other to that list, and then we can copy and paste into each of these, and now you can set it, using the little popup menu there. So that just touches the surface of what you can do using if statements and formulas using iWork Numbers. ‘Till next time, this is Gary with MacMost Now.