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.