Microsoft Office Tutorials and References
In Depth Information
Chapter 15: Performing Ma-
gic with Array Formulas
In This Chapter
• More examples of single-cell array formulas
• More examples of multicell array formulas
The previous chapter provided an introduction to arrays and array formulas, and also presented some basic ex-
amples to whet your appetite. This chapter continues the saga and provides many useful examples that further
demonstrate the power of this feature.
I selected the examples in this chapter to provide a good assortment of the various uses for array formulas. Most
can be used as-is. You will, of course, need to adjust the range names or references that you use. Also, you can
modify many of the examples easily to work in a slightly different manner.
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 contained 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 at this book's website. The file is named
single-cell array formulas.xlsx.
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 15-1 shows an example. The formula in cell D11 returns an
error value because the range that it sums (D4:D10) contains errors.
About the examples in this chapter