Excel 2010 Bible

Part II: Working with Formulas and Functions

386

Determining whether a range contains valid values .................................................

You may have a list of items that you need to check against another list. For example, you may

import a list of part numbers into a range named MyList, and you want to ensure that all the part

numbers are valid. You can do so by comparing the items in the imported list to the items in a

master list of part numbers (named Master).

The following array formula returns TRUE if every item in the range named MyList is found in the

range named Master. Both ranges must consist of a single column, but they don’t need to contain

the same number of rows.

{=ISNA(MATCH(TRUE,ISNA(MATCH(MyList,Master,0)),0))}

The array formula that follows returns the number of invalid items. In other words, it returns the

number of items in MyList that do not appear in Master.

{=SUM(1*ISNA(MATCH(MyList,Master,0)))}

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 demonstra-

tion 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 num-

ber of digits in the value in cell A1. For example, if cell A1 contains the value 409 , the LEN func-

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

{1,2,3}

Cross-Reference
For more information about using the INDIRECT function to return this array, see Chapter 16. n

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}