Office 2008 for Macintosh: The Missing Manual

Advanced Formula Magic

Chapter 12 covers the fundamentals of formulas—entering them manually, using the Formula Builder, and so on. The following section dives deeper into the heart of Excel’s mathematical power—its formulas.

Note

There’s a difference between formulas and functions. A formula is a calculation that uses an arithmetic operator (such as =A1+A2+A3+A4+A5), while a function is a canned formula that saves you the work of creating a formula yourself (such as =SUM(A1:A5)).

Because there’s no difference in how you use them, this chapter uses the terms interchangeably.

Nested Formulas

A nested formula is a formula that’s used as an argument (see the box on Looking up functions with the Formula Builder) to another formula. For example, in the formula =ABS(SUM(A1:A3)), the formula SUM(A1:A3) is nested within an absolute-value formula. When interpreting this formula, Excel first adds the contents of cells A1 through A3, and then finds the absolute value of that result—that’s the number you’ll see in the cell.

Nested formulas keep you from having to use other cells as placeholders; they’re also essential for writing compact formulas. In some cases (such as with the logical IF function), nesting lets you add real sophistication to your Excel spreadsheets by having Excel make decisions based on formula results.

The Formula Builder

The Formula Builder is a quick way of building powerful mathematical models in your spreadsheets. When activated, the Formula Builder shows every imaginable aspect ...