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.