Page 382 - Excel 2007 Bible

23_044039 ch18.qxp 11/21/06 11:09 AM Page 339
To return the first invalid item in MyList, use the following array formula:
{=INDEX(MyList,MATCH(TRUE,ISNA(MATCH(MyList,Master,0)),0))}
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 dig-
its 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} Performing Magic with Array Formulas 18
CROSS-REF For more information about using the INDIRECT function to return this array, see Chapter 17.
CROSS-REF
This array is then used as the second argument for the MID function. The MID part of the formula, simpli-
fied 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.
NOTE The values in the array created by the MID function are multiplied by 1 because the MID func-
NOTE
tion 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.
Notice that the formula doesn’t work with a negative value because the negative sign is not a numeric value.
The following formula solves this problem by using the ABS function to return the absolute value of the
number. Figure 18.4 shows a worksheet that uses this formula in cell B4.
{=SUM(VALUE(MID(ABS(A4),ROW(INDIRECT(“1:”&LEN(ABS(A4)))),1)))}
The formula was copied down to calculate the sum of the digits for other values in column A.

339