Microsoft Office Tutorials and References
In Depth Information
When you enter a multicell array formula, you must select the entire range first. Then type the formula and
press Ctrl+Shift+Enter.
The examples in this section are available at this book's website. 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-12, 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.
Search JabSto ::




Custom Search