Creating a simple IF-THEN formula

In this case you don't "REALLY" want and if()... you want min():

C3=MIN(24, C1+C2)

You can achieve a similar result using if():

D3=IF((C1+C2)<25,C1+C2, 24)

Building a little on what Wayne gave you, the actual syntax is basically the following:

IF( <some test that will result in a true or false>, <What to do if the test is true>, <What to do if False>)

The test is going to -almost- always going to include a cell reference and comparison sign, like A2=2 or like in Waynes above (C1+C2)<25.

The place to put what you would want if True or False contains the equation (note that it's without the equal sign). But it is the actual equation you would have typed by hand.

so in Waynes example (formatted with the areas with similar formatting to help you see where each part matches up):

IF( <some test that will result in a true or false>, <What to do if the test is true>, <What to do if False>)

IF((C1+C2)<25, C1+C2, 24)

Jason

Thank you Wayne Contello!

It helps. It makes sense.

But how I was suppose to know that I needed to change to semicolon as an argument delimiter?

Having a "kind" of a "Syntax Verifier" embedded in Numbers 09 would help a lot...

Best regards.

Pedro Cavilhas

Lisboa, Portugal

Hi Dean,

IF, and all the rest of the functions supported by the iWork applications, is defined and discussed in the iWork Formulas and Functions User Guide. A search for IF brings up a huge number of hits, of course ("if" is a very common word in this guide), but the one pointing to the Table of contents entry (on page 6) is third on the list, and not very hard to spot.

Clicking that will take you to the contents page section listing the Logical and Information functions, where clicking on the IF entry will take you to the article on this function, where you'll find a description, a statement of the syntax (not quite as easy to read as Jason's version), and an example or two of how to use the function.

For individual functions, though you might want to skip the search and go straight to the contents pages' list of functions, grouped by type. Starting there will point you to the page describing the function you're looking for AND show you some others you might not have considered.

Or you could use the Function Browser (without even leaving numbers). Click the yellow Function button in the Toolbar and choose Show Function Browser. Type the name (or part of the name) of the function you want in the search box. You'll get a list of all the functions that include the letter string you typed, plus other functions whose purpose is similar in some way. Select one, and the bottom part of the browser window gives you a description, including the syntax, for that function.

Click Insert Function and the browser inserts the function, with its syntax shown, into the selected cell. Click each segment in turn and write that part in.

One thing that search would not have produced in this case, though, is the suggestion made by Wayne to use MIN instead of IF. For the result you want, MIN makes much more sense than IF, so I'd second Wayne's proposed formula.

Regards,

Barry

Hi there!

I lost about 3 hours and I was really getting MAD with Apple Numbers 09

I'm not an advance user of Numbers 09, but I'm not a beginner either.

I have used formulas before with o problem.

I was trying to use formulas with function "IF" copying exactly as Apple examples but I always get "syntax error".

I have quitted the Application and started it again, started a spreadsheet from scratch, and I've restarted the computer and always get the same error.

I have tried the simple example presented in this discussion and also I get "Syntax error".

I was really getting MAD!

Incidentally I saw Barry post and clicked "Insert Function" in the "Formula Editor" and noticed a minor error syntax that I could have not resolve in spite my internet search: In all the places I have searched, including Apple Official Support pages (and Numbers Manual and Numbers Help) the separators of the conditions were comas ","

=IF(if-expression,if-true,f-false)

but my application only accept semicolon

=IF(if-expression;if-true;if-false)

Why is that?

Pedro

Pedro,

it is becuase the decimal in your localization is the "," not the ".". in other words...

you enter numbers like:

"1000,00" which is One Thousand two two decimal places.

since the "," is the decimal it cannot also be the arguement delimter so the semicolon is used.

I hope this helps

Pedro,

Check your Numbers User Guide and iWork Formulas and Functions User Guide.

You can download from the Help menu, hopefully in your country's language. See if the syntax for the functions in your manual are sensitive to this difference. They should be.

Jerry

Jerry:

Thank you very much for your replay

I have the "Numbers '09 User Guide" in Portuguese and as far as I'm concerned nothing is said about this.

In fact all the examples use coma "," as the argument delimiter and assume that "." is the decimal delimiter.

But it is true that my System Preferences is set to use coma "," as the decimal separator.

I would expect a warning for this matter or a standardized semicolon ";" as the universal argument delimiter.

Fortunately, after 3 lost hours, I've managed to format the cells as I wanted.

Best regards:

Pedro Cavilhas

Lisboa, Portugal

What if I want to have numbers check for a non Boolean entry and add together all numbers associated with that category. For example, in a budget I have multiple categories like Groceries, etc. I want to be able to find all items in each category and sum the individual items to produce a total for each category.

you can use a conditional test for a range of test values like:

=sumif(A, "Groceries", B)

column A refers to the test values

column B refers the sum value

you can replace the fixed test value "Groceries" with a cell reference is needed in which case the cell you reference would contains the text condition. When a cell in column A equals the value in the second argument ("Groceries" in this case) the corresponding value in the same row of column B is accumulated into the sum

I almost have it I think, however it's still not working. I have SUM(IF(Column C, "Groceries", column D))

Column C heading is Category

Column D heading is Amount

So it really reads SUM(IF(Category, "Groceries", Amount))

The error I get says, Argument 1 of IF expects a Boolean but found "Restaurant."

Pedro,

I'll bet that you are never puzzled for long by this error in the future.🙂

Jerry