Office 2010: The Missing Manual

Chapter 18. A Function Sampler and Formula Troubleshooting

Excel is packed with dozens and dozens of functions. It’s like having a team of number- and text-crunching spreadsheet jockeys by your desk, ready to help. (Yep, functions can also work their magic on your prose, doing things like joining together words, trimming out unwanted spaces, and serving as a kind of super-powered find-and-replace tool.) This chapter dips its toe into the giant pool of built-in functions. You’ll take a “greatest hits"-style tour and then wind things up with a look at a related and similarly helpful aid: the program’s formula evaluation and error-checking tools.

Rounding Numbers

Most people don’t devote enough thought to rounding, the process by which you adjust fractional numbers so they’re less precise but more manageable. For example, rounding can transform the unwieldy number 1.984323125 to 2. Excel has two ways to use rounding:

• Modify the number format of the cell. With this method, Excel rounds the displayed value, but it doesn’t change the underlying value. The advantage to this approach is that you can use the value in other calculations without losing any precision. When Excel rounds your numbers using this method, it simply rounds to the last displayed digit (rounding up if the next digit is 5 or greater).

  For example, if you tell Excel to show the number 3.145 using two decimal places, then Excel displays the rounded value of 3.15. (Cell value formatting is described in Chapter 16.)

• Use a