Part II: Working with Formulas and Functions
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.
Worksheet Functions That Return an Array
Several of the Excel worksheet functions use arrays; you must enter into multiple cells a formula that
uses one of these functions as an array formula. These functions include FORECAST, FREQUENCY, GROWTH,
LINEST, LOGEST, MINVERSE, MMULT, and TREND. Consult the Excel Help system for more information.
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 appar-
ent when you use single-cell array formulas. This section contains examples of array formu-
las that occupy a single cell.
Counting characters in a range
Suppose you have a range of cells that contains text entries (see Figure 18.14). 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:
=LEN(A1)
Then you use a SUM formula to calculate the sum of the values returned by these intermedi-
ate formulas.
The following array formula does the job without using any intermediate formulas:
{=SUM(LEN(A1:A14))}
The array formula uses the LEN function to create a new array (in memory) that consists of
the number of characters in each cell of the range. In this case, the new array is as follows:
{10,9,8,5,6,5,5,10,11,14,6,8,8,7}
The array formula is then reduced to the following:
=SUM({10,9,8,5,6,5,5,10,11,14,6,8,8,7})
