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))}