Excel 2010 Bible

Chapter 16: Introducing Array Formulas

371

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. Excel adjusts the row references so that 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.

Cross-Reference
Chapter 17 contains several examples that use the technique for generating consecutive integers. n

Using Single-Cell Array Formulas .....................................................................................

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

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

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

Counting characters in a range ................................................................................

Suppose that you have a range of cells that contains text entries (see Figure 16.14). If you need to

get a count of the total number of characters in that range, the “traditional” method involves creat-

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

=LEN(A1)

Then you use a SUM formula to calculate the sum of the values returned by these intermediate

formulas.

Several of the Excel worksheet functions use arrays; you must enter a formula that uses one of these func-

tions into multiple cells as an array formula. These functions are FORECAST, FREQUENCY, GROWTH,

LINEST, LOGEST, MINVERSE, MMULT, and TREND. Consult the Excel Help system for more information.

Worksheet Functions That Return an Array