Microsoft Office Tutorials and References
In Depth Information
Chapter 15: Performing Magic with Array Formulas
This formula generates an array of consecutive integers, and the MOD function uses this array as
its first argument. The second argument for the MOD function is the value of n. The MOD function
creates another array that consists of the remainders when each row number is divided by n.
When the array item is 0 (that is, the row is evenly divisible by n), the corresponding item in the
Data range will be included in the sum.
You find that this formula fails when n is 0 (that is, when it sums no items). The modified array
formula that follows uses an IF function to handle this case:
{=IF(n=0,0,SUM(IF(MOD(ROW(INDIRECT(“1:”&COUNT(data)))–1,n)=0,data,””)))}
This formula works only when the Data range consists of a single column of values. It does not
work for a multicolumn range or for a single row of values.
To make the formula work with a horizontal range, you need to transpose the array of integers
generated by the ROW function. Excel’s TRANPOSE function is just the ticket. The modified array
formula that follows works only with a horizontal Data range:
{=IF(n=0,0,SUM(IF(MOD(TRANSPOSE(ROW(INDIRECT(“1:”&COUNT(Data))))–
1,n)=0,Data,””)))}
Removing nonnumeric characters from a string
The following array formula extracts a number from a string that contains text. For example,
consider the string ABC145Z . The formula returns the numeric part, 145.
{=MID(A1,MATCH(0,(ISERROR(MID(A1,ROW(INDIRECT
(“1:”&LEN(A1))),1)*1)*1),0),LEN(A1)–SUM((ISERROR
(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)*1)*1)))}
This formula works only with a single embedded number. For example, it gives an incorrect result
with a string like X45Z99 because the string contains two embedded numbers.
Determining the closest value in a range
The formula in this section performs an operation that none of Excel’s lookup functions can do.
The array formula that follows returns the value in a range named Data that is closest to another
value (named Target ):
{=INDEX(Data,MATCH(SMALL(ABS(Target–Data),1),ABS(Target–Data),0))}

Search JabSto ::

Custom Search