Page 388 - Excel 2007 Bible

23_044039 ch18.qxp 11/21/06 11:09 AM Page 345
Performing Magic with Array Formulas
FIGURE 18.8
Ranking data with the Excel program’s RANK function and with array formulas.
Working with Multicell Array Formulas 18
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 of the values in a range, but
rearranged in some way.
The examples in this section are available on the companion CD-ROM. The file is named
ON the CD-ROM multi-cell array formulas.xlsx.
ON the CD-ROM
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 18.9, this formula works, but not perfectly. The Data range is A5:A24, and the
array formula is entered into C5:C24. However, the array formula displays #NUM! error values for cells that
don’t contain a value.
This modified array formula, entered into range E5:E24, use the IFERROR function to avoid the error value
display:
{=IFERROR(INDEX(Data,SMALL(IF(Data>0,ROW(INDIRECT(“1:”&ROWS(Data)))),RO
W(INDIRECT(“1:”&ROWS(Data))))),””)}

345