Posted by on 5/23/11

# MacMost Now 557: If Statements and Checkboxes in iWork Numbers

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.

Video Transcript (Click to Expand)
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.

### 32 Responses to “MacMost Now 557: If Statements and Checkboxes in iWork Numbers”

1. Antrim says:

Great episode. Very helpful. The alternative cell types were new to me.

2. Toby Zen says:

Excellent episode! As a Numbers user who’s trying to push the envelope with simulating back-end database functionality via multiple sheets of a Numbers doc, I would LOVE to hear even more goodies about achieving this. For instance:
1) Is it possible to have multiple-argument IF statements (If(C2 = “Visa”, (B2 * 1.0365), IF(C2 = “Amex”,(B2*1.0375), IF(C2 = “Master Card”,(B2*1.0345),B2)))
2) Is it possible to have Conditional (IF based) Pop up menus? for example, IF(C2 = “Magazine A”, Magazine A Ad Rate Card pop-up, Magazine B Ad Rate Card pop-up)
3)Is it possible to Unhide Hidden Rows, based on whether a checkbox in an earlier row is checked, for example, if a checkbox is checked for a general item in a small list of possible shopping items, only then would a hidden group of rows that allow detailed entry (color, size, style) for that item appear. It beats the alternative of always having EVERY detailed section unhidden, and have to hide everything not relevant in order to give a concise order form.
4) How, oh how can one copy and paste JUST THE CONDITIONAL FORMAT RULES to every cell below the top cell in a column.
5) Last but not least…How can we have conditional formatting rules based on the value of another cell or an IF statement?

any resources you recommend would be much appreciated!

Thanks and keep up the excellent work,

Toby

• 1. Should be. Try it.
2. I don’t think so.
3. I’m pretty sure, no.
4. Not sure.
5. Not sure.
I’d love to do more Numbers tutorials, but I’m afraid that only a small segment of the MacMost audience would be interested.

• Jef Wellens says:

” I’d love to do more Numbers tutorials, but I’m afraid that only a small segment of the MacMost audience would be interested.”

Just do them! Your audience will grow for sure!

Jef

• silvia says:

Hi!
I need to find a way to get a value from a cell.
Let’s say I have this:
goat € 3,00
Skin € 4,00
Tex € 0,50

If I choose “Goat”, in the cell I want to dispay the value “€ 3.00″. If I choose Skin I want in the cell the value “€ 4,00″ ecc.

How can I do this????

• Create a pop-up menu in one cell, with the values goat, skin and tex. Then put an IF statement in another cell that checks the value of that first cell and puts 3, 4 or .5 as a value accordingly.

Hi Gary,

I have inserted the IF statement as suggested above but cannot get the correct value to show. In my case I have a CLOSURE column for apparel closures and another CLOSURECOST with the cost of the closure. I would like to have the CLOSURECOST column’s value populate based on the variable I select in the pop-up menu (with 5 choices) that I have set up in the CLOSURE column.

Thank you.

• Check out the Help for an example of the IF statement, and build on that.

I have followed closely. It works with the first variable but I cannot get 2-5 to work can you provide the string please? Thank you for your help.
AO

• A better way might be to use HLOOKUP — take a look at that example. Create a small table that contains the 5 choices and the values that it represents. Or just LOOKUP, that might be even better.

3. Amir says:

Great tutorial, wish there was more about functions!

I was wondering how to go about inserting two checkboxes and if both are true give me the sum of the amount I want. So I thought it would be an IF statement.

Something like:
IF(B3,B2,0) AND(B4,B5+B3)

How can I create an argument with IF and AND?

• Look up the AND function in Numbers and you will see how it works.
So if the checkboxes were in B2 and B3, and the numbers to add were in B4 and B5, and you simply wanted a 0 to appear if they are not checked:
=IF(AND(B2,B3),B4+B5,0)

4. Mark says:

fantastic video, Like others I’m trying to push number to the limit of things it can do but unless i see these great tutorials i get very stuck.
Im with others please make more number tutorials.

5. Sree says:

Is it possible to enter a date and timestamp in another cell based on when the checkbox is selected? Also, is it possible to lock the checkbox once the timestamp is created?

• I don’t think so. But play around with those features to see.

• Sree says:

I explored it further and found that it does work. For the then condition use the NOW() function.

6. Steven Rohmer says:

Hello thank you for your tutorials. I wish you had a training course series that I could subscribe to. I am new to spreadsheets and numbers on the iPad. I would like to place amounts in cells with an adjacent cell showing percentage amounts of earnings, (commissions that i must pay from earnings to an agent). For instance, if the amounts are: 0-50, 5% commission; 50-99, 7%; 100 and over, 15%. I hope this question is clear enough. Thank you for any suggestions

• Many ways to do that. You could have three columns, each with a different IF statement. Say the amounts are in column B, with the first amount in B2. So in C2 you would have IF(B2>0,true,false) — that would but a TRUE in C2 if the amount was more than 0. Then D2 would be IF(B2>50,true,false) and E2 would be IF(B2>100,true,false). Then F2 would contain the formula =B2*.05*C2 + B2*.02*D2 + B2*.07*E2. That would give you commissions of .05, .07 and .15.
Of course you could also do it in one cell with a large IF formula
=B2*0.05*IF(B2>0,TRUE,FALSE)+B2*0.02*IF(B2>50,TRUE,FALSE)+B2*0.08*IF(B2>100,TRUE,FALSE)

7. Herbert says:

Great tutorial. Can you use if statements to edit/format cells? Eg. If a particular item is < 0, I want to format the cell to be red color from original grey color. I remember this could be done in excel.wondering if it could be done using numbers. Thanks in advance.

8. Kika Wai'Alae says:

Yes we need more on Numbers please. I am trying to create a work order template. I would need to have a list of 20 items each having a check box in one table. When a box is checked I want that item name to appear in a description cell and the price in the price cell in another table. Following your example here, when the box is checked name appears. When not checked “False” appears. I still cannot understand how to create the if statement to combine all the actions. 1- when box checked insert name of item in the description cell while 2-insert cost in the unit price cell. If not checked I donʻt want “False” to appear in the description cell. Out of the 20 items w/chk boxes I may select multiple boxes of which they should appear in any available description cell. Wow Iʻm confused, help. Thanks

• I’d read up on the IF and other similar functions in the Help. Then maybe try creating a test worksheet and try things with it. Experiment to learn and get better at using those functions.

9. Kika Wai'Alae says:

Can you recommend a link or book to learn IF and Functions for Numbers to get a better understanding? I purchased your book but it doesn’t talk much about that subject understandably so. I really am clueless in creating forms yet alone formulas. Thanks again sir.

• I’d look for a good book or resource for spreadsheets in general. Numbers formulas are almost the same as Excel formulas, so learning Excel or spreadsheet formulas in general is what you want. I don’t have any specific recommendations, sorry.

10. MT says:

How can you do a conditional formula for a word within a text box.
Ex. If a Comments field includes the word “HOLD” I want the field to be counted in another column. However the word HOLD would not be the only word in that field.
Column 1 Column 2
Project A Problem put project on HOLD.
Project B Project on HOLD until until next quarter.
In this case how to setup a count in separate table that tracks Column 2 results so that it see 2 HOLD projects.

• Any time you need something like this just look in the function reference. In this case you’ll find the SEARCH function. It will return the position of the string in another string. It returns an error if the string isn’t there, so use ISERROR to detect that. And here is the start of what you need. I’ll use NOT to reverse it (TRUE if the string is there, FALSE if it isn’t there):
=NOT(ISERROR(SEARCH(“hold”,CELLREFERENCE)))

11. MT says:

Thanks Gary, Ive been playing around with this but it seems to be hit or miss on whether the search finds the string. Even when I test by having the search word be the only word in the field.
Simiarly Ive a number cell that handles percentages that can be typed in or a sliding bar can be used to input. I cant get the forumula listed above to count how many are of a particular percentage.

• Shouldn’t be hit or miss. It should work perfectly. Maybe take a closer look at what you are trying to match and the formula you have created?
Perhaps your sliding bar counting isn’t working because you are trying for precise numbers when a sliding bar gives you a range? Like a bar is really 49.5 instead of 50. Experiment. Play around with it. You’ll figure it out — the answer may not be the first thing you try. If you need, perhaps get help from a spreadsheet expert.

12. Wesley says:

Great tutorial! Using the video tutorial above, imagine if the prices were replaced with strings (e.g., “eggs”, “milk”, “bread”). Is it possible to use checkboxes to combine the strings into single cell in Column D that’s formatted as a bulleted list? As a result, you end up with a bulleted list of items that you checked in column B.

• Not sure. Would take some creative formulas. If your goal is a bullet list: not sure if a single cell is the best repository for that.

• Wesley says:

I’ve tried a VLOOKUP but it only returns a single value. The main goal is to only reference the items selected. It doesn’t have to be a bulleted list in a single cell. Any ideas?

• Sorry, I’m just not getting what you are trying to do. Maybe this isn’t a task suited for a spreadsheet? Or, maybe you need a one-on-one consultation with a spreadsheet expert.