Excel 2010 Bible

Chapter 17: Performing Magic with Array Formulas

395

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

FIGURE 17.9

Using an array formula to return only the positive values in a range.

Reversing the order of cells in a range .....................................................................

In Figure 17.10, cells C4:C13 contain a multicell array formula that reverses the order of the values

in the range A4:A13 (which is named Data).

The array formula is

{=IF(INDEX(Data,ROWS(Data)-ROW(INDIRECT

(“1:”&ROWS(Data)))+1)=””,””,INDEX(Data,ROWS(Data)-ROW(INDIRECT(“1

:”&ROWS(Data)))+1))}