Microsoft Office Tutorials and References
In Depth Information
Working with Multicell Array Formulas
Figure 15-10: A multicell array formula displays the entries in A4:A13 in reverse order.
Sorting a range of values dynamically
Figure 15-11 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)))))}
Note that this formula works only with values. The companion CD-ROM has a similar array
formula example that works only with text.
Returning a list of unique items in a range
If you have a single-column range named Data, the following array formula returns a list of the
unique items in the range (the list with no duplicated items):
{=INDEX(Data,SMALL(IF(MATCH(Data,Data,0)=ROW(INDIRECT
(“1:”&ROWS(Data))),MATCH(Data,Data,0),””),ROW(INDIRECT
(“1:”&ROWS(Data)))))}
 
Search JabSto ::




Custom Search