iWork '09: The Missing Manual

What IF: Adding Logic to Formulas

All the functions you’ve seen so far have performed straight-up calculations. They’ve taken a set of values, worked a predefined transformation on them, and returned the value, no questions asked. For the functions in the Function Browser’s “Logical and Information” category, however, asking questions is the whole point. These discriminating functions sit your data down for an interview, ask probing questions, and then decide what to do based on what they discover:

• "Is your grade higher than 93 percent? Yes? Congratulations, you get an A.”

• "Have you paid your January bill? No? The amount goes into accounts receivable.”

• "Has this check cleared? Yes? Add it to the reconciled balance in the check register.”

This category of functions imposes true-or-false conditions on your data: If yes, do this; if no, do that. It’s like the game Twenty Questions: The “questions” these functions ask can be complex, but the answers are always simple: yes or no. It sounds basic, but the result can be quite powerful, even letting you sift through mountains of data to pluck out summaries of what’s found within. Before tackling the mountains, though, you’ll start with a molehill, learning how to set a single cell’s value based on one or more conditions.

Setting Conditions with the IF Function

The IF function is Numbers’ general-purpose decision-maker. It checks to see if some condition is true and, based on the outcome, it does either one thing or the other. You make this kind ...