Page 383 - Excel 2007 Bible

23_044039 ch18.qxp 11/21/06 11:09 AM Page 340
Part II
Working with Formulas and Functions
FIGURE 18.4
An array formula calculates the sum of the digits in an integer.
Summing rounded values
Figure 18.5 shows a simple worksheet that demonstrates a common spreadsheet problem: rounding 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 18.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 deci-
mal places:
{=SUM(ROUND(E4:E6,2))}
This formula returns $168.31.

340