Microsoft Office Tutorials and References

In Depth Information

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 consecut-

ive 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 single-cell 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-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 fol-

lows and copying it down the column:

=LEN(A1)

Then, you use a SUM formula to calculate the sum of the values returned by the intermediate 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 char-

acters in each cell of the range. In this case, the new array is

{10,9,8,5,6,5,5,10,11,14,6,8,8,7}