Excel 2010 Bible

Part II: Working with Formulas and Functions

388

Summing rounded values ........................................................................................

Figure 17.5 shows a simple worksheet that demonstrates a common spreadsheet problem: round-

ing errors. As you can see, the grand total in cell E7 appears to display an incorrect amount. (That

is, it’s off by a penny.) The values in column E use a number format that displays two decimal

places. The actual values, however, consist of additional decimal places that do not display due to

rounding (as a result of the number format). The net effect of these rounding errors is a seemingly

incorrect total. The total, which is actually $168.320997, displays as $168.32.

FIGURE 17.5

Using an array formula to correct rounding errors.

The following array formula creates a new array that consists of values in column E, rounded to

two decimal places:

{=SUM(ROUND(E4:E6,2))}

This formula returns $168.31.

You also can eliminate these types of rounding errors by using the ROUND function in the formula

that calculates each row total in column E (which does not require an array formula).

Summing every nth value in a range ........................................................................

Suppose that you have a range of values and you want to compute the sum of every third value in

the list — the first, the fourth, the seventh, and so on. One solution is to hard-code the cell

addresses in a formula. A better solution, though, is to use an array formula.

Note
In Figure 17.6, the values are stored in a range named Data, and the value of n is in cell D2 (named n). n