An array formula example

Excel's array formulas enable you to perform individual operations on each cell in a range in much the same way that a program language's looping feature enables you to work with elements of an array. If you've never used array formulas before, this section will get your feet wet with a hands-on example.

Figure 3-5 shows a worksheet with text in A1:A5. The goal of this exercise is to create a single formula that returns the sum of the total number of characters in the range. Without the single formula requirement, you would write a formula with the LEN function, copy it down the column, and then use the SUM function to add the results of the intermediate formulas.

Figure 3-5: Cell B1 contains an array formula that returns the total number of characters contained in range A1:A5.

To demonstrate how an array formula can occupy more than one cell, create the worksheet shown in Figure 3-5, and then try this:

1. Select the range B1:B5.

2. Type the following formula:

3. Press Ctrl+Shift+Enter.

The preceding steps enter a single array formula into five cells. Enter a SUM formula that adds the values in B1:B5, and you'll see that the total number of characters in A1:A5 is 24.

Here's the key point: It's not necessary to actually display those five array elements. Rather, Excel can store the array in memory. Knowing this, you can type the following single formula in any blank cell. Remember: Make sure that you enter it by pressing Ctrl+Shift+Enter:

This formula is displayed in the formula bar surrounded by brackets (refer to Figure 3-5):

This formula essentially creates a five-element array (in memory) that consists of the length of each string in A1:A5. The SUM function uses this array as its argument, and the formula returns 27.

Was this article helpful?

0 0

Post a comment