Want to know more about how to use Numbers on your Mac?
You can also watch this video at YouTube (but with ads).
MacMost Now 557: If Statements and Checkboxes in iWork Numbers
Comments: 32 Responses to “MacMost Now 557: If Statements and Checkboxes in iWork Numbers”
Comments Closed.
MacMost Online Courses
Keyboard Shortcuts PDF
Great episode. Very helpful. The alternative cell types were new to me.
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.
" 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
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.
Adam
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.
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?
Thanks in advance.
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)
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.
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.
I explored it further and found that it does work. For the then condition use the NOW() function.
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)
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.
What you want is called "conditional formatting." See http://macmost.com/iwork-numbers-cell-formatting.html
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.
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.
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)))
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.
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.
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.