Page 376 - Excel 2007 Bible

23_044039 ch18.qxp 11/21/06 11:09 AM Page 333
Performing Magic with
Array Formulas
he preceding chapter provides an introduction to arrays and array formu-
las and presented some basic examples to whet your appetite. This chap- IN THIS CHAPTER
Tter continues the saga and provides many useful examples that further More examples of single-cell
demonstrate the power of this feature.
array formulas
I selected the examples in this chapter to provide a good assortment of the vari-
ous uses for array formulas. You can use most of them as-is. You will, of course, More examples of multicell array
need to adjust the range names or references used. Also, you can modify many of formulas
the examples easily to work in a slightly different manner.
Returning an array from a
custom VBA function
Working with Single-Cell Array
Formulas
As I describe in the preceding chapter, you enter single-cell array formulas into a
single cell (not into a range of cells). These array formulas work with arrays con-
tained in a range or that exist in memory. This section provides some additional
examples of such array formulas.
The examples in this section are available on the companion
ON the CD-ROM CD-ROM. The file is named single-cell array formulas.xlsx.
ON the CD-ROM
Summing a range that contains errors
You may have discovered that the SUM function doesn’t work if you attempt to
sum a range that contains one or more error values (such as #DIV/0! or #N/A).
Figure 18.1 shows an example. The formula in cell C11 returns an error value
because the range that it sums (C4:C10) contains errors.
The following array formula, in cell C13, overcomes this problem and returns the
sum of the values, even if the range contains error values:

{=SUM(IF(ISERROR(C4:C10),””,C4:C10))}

333