Microsoft Office Tutorials and References
In Depth Information
{=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))}
Figure 15-6: Using an array formula to count and identify items that aren't in a list.
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 po-
tential power of an array formula. The following array formula calculates the sum of the digits in a positive in-
teger, 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:
Search JabSto ::




Custom Search