Microsoft Office Tutorials and References
In Depth Information
Working with Multicell Array Formulas
Working with Multicell Array Formulas
The preceding chapter introduces array formulas that you can enter into multicell ranges. In this
section, I present a few more array multicell formulas. Most of these formulas return some or all
of the values in a range, but are rearranged in some way.
The examples in this section are available on the companion CD-ROM. The file is named
multi-cell array formulas.xlsx .
Returning only positive values from a range
The following array formula works with a single-column vertical range (named Data ). The array
formula is entered into a range that’s the same size as Data and returns only the positive values
in the Data range. (Zeroes and negative numbers are ignored.)
{=INDEX(Data,SMALL(IF(Data>0,ROW(INDIRECT(“1:”&ROWS(Data)))),
ROW(INDIRECT(“1:”&ROWS(Data)))))}
As you can see in Figure 15-9, this formula works, but not perfectly. The Data range is A4:A23,
and the array formula is entered into C4:C23. However, the array formula displays #NUM! error
values for cells that don’t contain a value.
Figure 15-9: Using an array formula to return only the positive values in a range.
 
Search JabSto ::




Custom Search