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 programming 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-6 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.

 J =r= * Nit Home Insert Pag t .ayout Formulas pi ¿ Cut Calibri '111 * A* A* | -J Cepv / Fofimit Painter B Jt tJ -hs - - A '1 CliflbiJrd n Font Hi 81 w f* í=SUM(LEN(Al:A5f)> A B C D E 1 rab hit 2, i 2 coyote 3 Ciusll 4 deer 5 javehra 6

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

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

To demonstrate how an array formula can occupy more than one cell, create the worksheet shown in the figure 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 29.

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 array formula in any blank cell (Remember: Don't type the curly brackets, and make sure that you enter it by pressing Ctrl+Shift+Enter):

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 29.

0 0