Chapter 17: Performing Magic with Array Formulas
387
This function generates an array with three elements:{4,0,9}By simplifying again and adding the SUM function, the formula looks like this:{=SUM({4,0,9})}This formula produces the result of 13.Note
The values in the array created by the MID function are multiplied by 1 because the MID function returns a
string. Multiplying by 1 forces a numeric value result. Alternatively, you can use the VALUE function to force a
numeric string to become a numeric value. n
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))}Note
This formula uses the IFERROR function, which was introduced in Excel 2007.Figure 17.4 shows a worksheet that uses both versions of this formula.FIGURE 17.4
Two versions of an array formula calculate the sum of the digits in an integer.