Part II: Working with Formulas and Functions
394
Working with Multicell Array Formulas ............................................................................
The preceding chapter introduced array formulas entered into multicell ranges. In this section, I
present a few more array multicell formulas. Most of these formulas return some or all the values
in a range, but rearranged in some way.On the CD
The examples in this section are available on the companion CD-ROM. The file is named multi-cell array
formulas.xlsx.
Returning only positive values from a range ............................................................
The following array formula works with a single-column vertical range (named Data). The array
formula is entered into a range that’s the same size as Data and returns only the positive values in
the Data range. (Zeroes and negative numbers are ignored.){=INDEX(Data,SMALL(IF(Data>0,ROW(INDIRECT(“1:”&ROWS(Data)))),
ROW(INDIRECT(“1:”&ROWS(Data)))))}As you can see in Figure 17.9, this formula works, but not perfectly. The Data range is A4:A22,
and the array formula is entered into C4:C23. However, the array formula displays #NUM! error
values for cells that don’t contain a value.This modified array formula, entered into range E4:E23, uses the IFERROR function to avoid the
error value display:{=IFERROR(INDEX(Data,SMALL(IF(Data>0,ROW
(INDIRECT(“1:”&ROWS(Data)))),ROW
(INDIRECT(“1:”&ROWS(Data))))),””)}The IFERROR function was introduced in Excel 2007. For compatibility with older versions, use
this formula:{=IF(ISERR(SMALL(IF(Data>0,ROW(INDIRECT(“1:”&ROWS(Data)))),ROW
(INDIRECT(“1:”&ROWS(Data))))),””,INDEX(Data,SMALL(IF
(Data>0,ROW(INDIRECT(“1:”&ROWS(Data)))),ROW(INDIRECT
(“1:”&ROWS(Data))))))}Returning nonblank cells from a range ....................................................................
The following formula is a variation on the formula in the preceding section. This array formula
works with a single-column vertical range named Data. The array formula is entered into a range
of the same size as Data and returns only the nonblank cell in the Data range.