Microsoft Office Tutorials and References

In Depth Information

**Chapter 15: Performing Magic with Array Formulas**

15

Performing Magic 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 examples 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 on the companion CD-ROM. 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 C11 returns an error value because the range that it sums (C4:C10) contains errors.