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
zero:
{=SUM(IFERROR(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)*1,0))}
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.
 
Search JabSto ::




Custom Search