Microsoft Office Tutorials and References
In Depth Information
Chapter 15: Performing Magic with Array Formulas
Notice that the formula doesn’t work with a negative value because the negative sign is not a
numeric value. Also, the formula fails if the cell contains non-numeric values (such as 123A6). The
following formula solves this problem by checking for errors in the array and replacing them with
This formula uses the IFERROR function, which was introduced in Excel 2007.
Figure 15-4 shows a worksheet that uses both versions of this formula.
Figure 15-4: Two versions of an array formula that calculates the sum of the digits in an integer.
Summing rounded values
Figure 15-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 15-5: Using an array formula to correct rounding errors.