Page 389 - Excel 2007 Bible

23_044039 ch18.qxp 11/21/06 11:09 AM Page 346
Part II
Working with Formulas and Functions
The IFERROR function is new to 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))))))}
FIGURE 18.9
Using an array formula to return only the positive values in a range.

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.

{=IFERROR(INDEX(Data,SMALL(IF(Data<>””,ROW(INDIRECT(“1:”&ROWS(Data)))),
ROW(INDIRECT(“1:”&ROWS(Data))))),””)}
For compatibility with versions prior to Excel 2007, use this formula:
{=IF(ISERR(SMALL(IF(Data<>””,ROW(INDIRECT(“1:”&ROWS(Data)))),
ROW(INDIRECT(“1:”&ROWS(Data))))),””,INDEX(Data,SMALL(IF(Data
<>””,ROW(INDIRECT(“1:”&ROWS(Data)))),ROW(INDIRECT(“1:”&ROWS
(Data))))))}

346