Microsoft Office Tutorials and References
In Depth Information
{=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.
{=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))))))}
Reversing the order of cells in a range
In Figure 15-13, 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))}
Search JabSto ::

Custom Search