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




Custom Search