Chapter 18: Performing Magic with Array Formulas

In This Chapter

Looking at more examples of single-cell array formulas

Seeing more examples of multicell array formulas

Returning an array from a custom VBA function

The preceding chapter provides an introduction to arrays and array formulas and 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. You can use most of them as is. You will, of course, need to adjust the range names or references used. 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.

On the Web

The examples in this section are available on 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 18.1 shows an example. The formula in cell D11 returns an error value because the ...