Microsoft Office Tutorials and References

In Depth Information

**Using Single-Cell Array Formulas**

Worksheet functions that return an array

Several of Excel’s worksheet functions use arrays; you must enter a formula that uses one of

these functions into multiple cells as an array formula. These functions are as follows:

FORECAST, FREQUENCY, GROWTH, LINEST, LOGEST, MINVERSE, MMULT, and TREND. Consult

the online help for more information.

If you want to generate an array of consecutive integers, a formula like the one shown previously

is good — but not perfect. To see the problem, insert a new row above the range that contains

the array formula. You’ll find that Excel adjusts the row references so the array formula now

reads:

{=ROW(2:13)}

The formula that originally generated integers from 1 to 12 now generates integers from 2 to 13.

For a better solution, use this formula:

{=ROW(INDIRECT(“1:12”))}

This formula uses the INDIRECT function, which takes a text string as its argument. Excel does

not adjust the references contained in the argument for the INDIRECT function. Therefore, this

array formula
always
returns integers from 1 to 12.

Chapter 15 contains several examples that use the technique for generating consecutive

integers.

Using Single-Cell Array Formulas

The examples in the previous section all used a multicell array formula — a single array formula

entered into a range of cells. The real power of using arrays becomes apparent when you use

singlecell array formulas. This section contains examples of array formulas that occupy a single cell.

Counting characters in a range

Suppose you have a range of cells that contains text entries (see Figure 14-13). If you need to get

a count of the total number of characters in that range, the traditional method involves creating a

formula like the one that follows and copying it down the column: