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.