iWork: The Missing Manual

Chapter 20. Advanced Data Crunching with Functions

You can get lots done in your spreadsheets simply by mixing formulas with the ingredients you’ve seen so far: cell references, operators, and constant values. You could put down this book right now and be wildly productive with Numbers—the whole world of basic algebra is your oyster. But the great thing is that you don’t have to do the algebra (or eat the oyster, for that matter). Numbers gives you more than 250 predefined calculations called functions that juggle figures so you don’t have to construct the equations yourself. They can even perform a few tasks that are flat-out impossible to do with straight-up arithmetic.

Functions can handle a range of jobs including simple addition, converting teaspoons to tablespoons, and figuring out an asset’s depreciation. They’re not limited to working with numbers, either. Functions can transform text, traverse the calendar, construct sentences, make logical decisions, and sift through tables to find certain values. This chapter shows you how to put this remarkable collection of timesavers to use in your formulas. You’ll start with the most common tasks and move on to sophisticated strategies for wrangling data across multiple tables.

How Functions Work

Think of Numbers’ built-in function library as an army of math-minded elves waiting to do your bidding. Each elf has a specific job it knows how to do; you give it the raw materials it needs to do that job—a set of numbers to average, for example—and ...