Microsoft Office Tutorials and References

In Depth Information

Figure 15-13:
A multicell array formula displays the entries in A4:A13 in reverse order.

Sorting a range of values dynamically

Figure 15-14 shows a data entry range in column A (named
Data
). As the user enters values into that range, the

values are displayed sorted from largest to smallest in column C. The array formula in column C is rather

simple:

{=LARGE(Data,ROW(INDIRECT(“1:”&ROWS(Data))))}

If you prefer to avoid the #NUM! error display, the formula gets a bit more complex:

{=IF(ISERR(LARGE(Data,ROW(INDIRECT(“1:”&ROWS(Data))))),

“”,LARGE(Data,ROW(INDIRECT(“1:”&ROWS(Data)))))}

If you require compatibility with versions prior to Excel 2007, the formula gets a bit more complex:

{=IF(ISERR(LARGE(Data,ROW(INDIRECT(“1:”&ROWS(Data))))),””,LARGE(Data,ROW(INDIRECT(“1:”&ROWS(Data)))))}

Note that this formula works only with values. The file at this book's website has a similar array formula ex-

ample that works only with text.