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