Microsoft Office Tutorials and References

In Depth Information

**Chapter 15: Performing Magic with Array Formulas**

Summing the digits of an integer

I can’t think of any practical application for the example in this section, but it’s a good

demonstration of the power of an array formula. The following array formula calculates the sum of the

digits in a positive integer, which is stored in cell A1. For example, if cell A1 contains the value

409, the formula returns 13 (the sum of 4, 0, and 9).

{=SUM(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)*1)}

To understand how this formula works, start with the ROW function, as shown here:

{=ROW(INDIRECT(“1:”&LEN(A1)))}

This function returns an array of consecutive integers beginning with 1 and ending with the

number of digits in the value in cell A1. For example, if cell A1 contains the value 409, the LEN

function returns 3, and the array generated by the ROW functions is

{1,2,3}

For more information about using the INDIRECT function to return this array, see

Chapter 14.

This array is then used as the second argument for the MID function. The MID part of the formula,

simplified a bit and expressed as values, is the following:

{=MID(409,{1,2,3},1)*1}

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.

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.