Office 2013: The Missing Manual

Chapter 19. Math and Statistical Functions

Excel is packed with dozens of mathematical functions. Some of these functions are for specialist audiences, like engineers or statisticians, while others are so useful they can turn up in almost any civilian’s spreadsheet.

Rather than slog through each function one by one, this chapter covers the most useful functions in each category. It starts by looking at a bunch of functions that help round, add, and count numbers. You’ll also learn a bunch of techniques for excising errors.

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 lets you round numbers two ways:

• Modify the number format of the cell. With this method, Excel rounds the displayed value, but 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, Excel displays the rounded value of 3.15. (Cell value formatting is described in Chapter 17.)

• Use a rounding function. This approach gives you more control. For example, you can round a number before you use it in another calculation, ...