Figure 17.1 shows a simple worksheet set up to calculate product sales. Normally, you’d calculate the value in column D (total sales per product) with a formula such as the one that follows, and then you’d copy this formula down the column.

=B2*C2

Figure 17.1. Column D contains formulas to calculate the total for each product.

After copying the formula, the worksheet contains six formulas in column D.

An alternative method uses a single formula (an array formula) to calculate all six values in D2:D7. This single formula occupies six cells and returns an array of six values.

To create a single array formula to perform the calculations, follow these steps:

The formula is entered into all six of the selected cells. If you examine the Formula bar, you see the following:

{=B2:B7*C2:C7}

Excel places curly brackets around the formula to indicate that it’s an array formula.

This formula performs its calculations and returns a six-item array. The array formula actually works with two other arrays, both of which happen to be stored in ranges. The values for the first array are stored in B2:B7, and the values for the second array are stored in C2:C7.

Because you can’t display more than one value in a single cell, six cells are required to display the resulting array—which explains why you selected six cells before you entered the array formula.

This array formula, of course, returns exactly the same values as these six normal formulas entered into individual cells in D2:D7:

=B2*C2
=B3*C3
=B4*C4
=B5*C5
=B6*C6
=B7*C7

Using a single array formula rather than individual formulas does offer a few advantages:

Using a multicell array formula as described in the preceding list also has some potential disadvantages:

Now it’s time to take a look at a single-cell array formula. Check out Figure 17.2, which is similar to Figure 17.1. Notice, however, that the formulas in column D have been deleted. The goal is to calculate the sum of the total product sales without using the individual calculations that were in column D.

Figure 17.2. The array formula in cell C10 calculates the total sales without using intermediate formulas.

The following array formula is in cell C10:

{=SUM(B2:B7*C2:C7)}

When you enter this formula, make sure that you use Ctrl+Shift+Enter (and don’t type the curly brackets).

This formula works with two arrays, both of which are stored in cells. The first array is stored in B2:B7, and the second array is stored in C2:C7. The formula multiplies the corresponding values in these two arrays and creates a new array (which exists only in memory). The SUM function then operates on this new array and returns the sum of its values.

=SUMPRODUCT(B2:B7,C2:C7)

The examples in the preceding section used arrays stored in worksheet ranges. The examples in this section demonstrate an important concept: An array need not be stored in a range of cells. This type of array, which is stored in memory, is referred to as an array constant.

To create an array constant, list its items and surround them with brackets. Here’s an example of a five-item vertical array constant:

{1,0,1,0,1}

The following formula uses the SUM function, with the preceding array constant as its argument. The formula returns the sum of the values in the array (which is 3).

=SUM({1,0,1,0,1})

Notice that this formula uses an array, but the formula itself isn’t an array formula. Therefore, you don’t use Ctrl+Shift+Enter to enter the formula—although entering it as an array formula will still produce the same result.

At this point, you probably don’t see any advantage to using an array constant. The formula that follows, for example, returns the same result as the previous formula:

=SUM(1,0,1,0,1)

The advantages, however, will become apparent.

Following is a formula that uses two array constants:

=SUM({1,2,3,4}*{5,6,7,8})

This formula creates a new array (in memory) that consists of the product of the corresponding elements in the two arrays. The new array is

{5,12,21,32}

This new array is then used as an argument for the SUM function, which returns the result (70). The formula is equivalent to the following formula, which doesn’t use arrays:

=SUM(1*5,2*6,3*7,4*8)

Alternatively, you can use the SUMPRODUCT function. The formula that follows is not an array formula, but it uses two array constants.

=SUMPRODUCT({1,2,3,4},{5,6,7,8})

A formula can work with both an array constant and an array stored in a range. The following formula, for example, returns the sum of the values in A1:D1, each multiplied by the corresponding element in the array constant:

=SUM((A1:D1*{1,2,3,4}))

This formula is equivalent to:

=SUM(A1*1,B1*2,C1*3,D1*4)

An array constant can contain numbers, text, logical values (TRUE or FALSE), and even error values, such as #N/A. Numbers can be in integer, decimal, or scientific format. You must enclose text in double quotation marks. You can use different types of values in the same array constant, as in this example:

{1,2,3,TRUE,FALSE,TRUE,"Moe","Larry","Curly"}

An array constant can’t contain formulas, functions, or other arrays. Numeric values can’t contain dollar signs, commas, parentheses, or percent signs. For example, the following is an invalid array constant:

{SQRT(32),$56.32,12.5%}

The elements in a one-dimensional horizontal array are separated by commas. The following example is a one-dimensional horizontal array constant:

{1,2,3,4,5}

To display this array in a range requires five consecutive cells in a row. To enter this array into a range, select a range of cells that consists of one row and five columns. Then enter ={1,2,3,4,5} and press Ctrl+Shift+Enter.

The following example is another horizontal array; it has seven elements and is made up of text strings:

{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"}

To enter this array, select seven cells in a row and type the following (followed by Ctrl+Shift+Enter):

={"Sun","Mon","Tue","Wed","Thu","Fri","Sat"}

The elements in a one-dimensional vertical array are separated by semicolons. The following is a six-element vertical array constant:

{10;20;30;40;50;60}

Displaying this array in a range requires six cells in a column. To enter this array into a range, select a range of cells that consists of six rows and one column. Then enter the following formula, followed by Ctrl+Shift+Enter:

={10;20;30;40;50;60}

The following is another example of a vertical array; this one has four elements:

{"Widgets";"Sprockets";"Doodads";"Thingamajigs"}

A two-dimensional array uses commas to separate its horizontal elements and semicolons to separate its vertical elements. The following example shows a 3 × 4 array constant:

{1,2,3,4;5,6,7,8;9,10,11,12}

Displaying this array in a range requires 12 cells. To enter this array into a range, select a range of cells that consists of three rows and four columns. Then type the following formula, followed by Ctrl+Shift+Enter:

={1,2,3,4;5,6,7,8;9,10,11,12}

Figure 17.3 shows how this array appears when entered into a range (in this case, B2:E4).

Figure 17.3. A 3 × 4 array entered into a range of cells.

If you enter an array into a range that has more cells than array elements, Excel displays #N/A in the extra cells. Figure 17.4 shows a 3 × 4 array entered into a 10 × 5 cell range.

Figure 17.4. A 3 × 4 array entered into a 10 × 5 cell range.

Each row of a two-dimensional array must contain the same number of items. The array that follows, for example, isn’t valid, because the third row contains only three items:

{1,2,3,4;5,6,7,8;9,10,11}

Excel doesn’t allow you to enter a formula that contains an invalid array.

When you enter an array formula into a cell or range, you must follow a special procedure so that Excel knows that you want an array formula rather than a normal formula. You enter a normal formula into a cell by pressing Enter. You enter an array formula into one or more cells by pressing Ctrl+Shift+Enter.

Don’t enter the curly brackets when you create an array formula; Excel inserts them for you. If the result of an array formula consists of more than one value, you must select all the cells in the results range before you enter the formula. If you fail to do so, only the first element of the result is returned.

You can select the cells that contain a multicell array formula manually by using the normal cell selection procedures. Or you can use either of the following methods:

If an array formula occupies multiple cells, you must edit the entire range as though it were a single cell. The key point to remember is that you can’t change just one element of an array formula. If you attempt to do so, Excel displays the message shown in Figure 17.7.

Figure 17.7. Excel’s warning message reminds you that you can’t edit just one cell of a multicell array formula.

The following rules apply to multicell array formulas. If you try to do any of these things, Excel lets you know about it:

To edit an array formula, select all the cells in the array range and activate the Formula bar as usual (click it or press F2). Excel removes the brackets from the formula while you edit it. Edit the formula and then press Ctrl+Shift+Enter to enter the changes. All the cells in the array now reflect your editing changes.

Although you can’t change any individual cell that makes up a multicell array formula, you can apply formatting to the entire array or to only parts of it.

Often, you may need to expand a multicell array formula (to include more cells) or contract it (to include fewer cells). Doing so requires a few steps:

The following array formula creates an array from a range of cells. Figure 17.8 shows a workbook with some data entered into A1:C4. The range D8:F11 contains a single array formula:

{=A1:C4}

Figure 17.8. Creating an array from a range.

The array in D8:F11 is linked to the range A1:C4. Change any value in A1:C4, and the corresponding cell in D8:F11 reflects that change. It’s a one-way link, of course. You can change a value in D8:F11.

In the preceding example, the array formula in D8:F11 essentially created a link to the cells in A1:C4. It’s possible to sever this link and create an array constant made up of the values in A1:C4:

The array constant is

{1, "dog" ,3;4,5, "cat";7,8,9; "monkey",11,12}

Figure 17.9 shows how this looks in the Formula bar.

Figure 17.9. After you’ve pressed F9, the Formula bar displays the array constant.

So far, most of the examples in this chapter simply entered arrays into ranges. The following array formula creates a rectangular array and multiplies each array element by 2:

{={1,2,3,4;5,6,7,8;9,10,11,12}*2}

Figure 17.10 shows the result when you enter this formula into a range:

Figure 17.10. Performing a mathematical operation on an array.

The following array formula multiplies each array element by itself. Figure 17.11 shows the result when you enter this formula into a range:

{={1,2,3,4;5,6,7,8;9,10,11,12}*{1,2,3,4;5,6,7,8;9,10,11,12}}

Figure 17.11. Multiplying each array element by itself.

The following array formula is a simpler way of obtaining the same result:

{={1,2,3,4;5,6,7,8;9,10,11,12}^2}

If the array is stored in a range (such as A1:C4), the array formula returns the square of each value in the range, as follows:

{=A1:C4^2}

As you may expect, you also can use functions with an array. The following array formula, which you can enter into a 10-cell vertical range, calculates the square root of each array element in the array constant:

{=SQRT({1;2;3;4;5;6;7;8;9;10})}

If the array is stored in a range, an array formula such as the one that follows returns the square root of each value in the range:

{=SQRT(A1:A10)}

When you transpose an array, you essentially convert rows to columns and columns to rows. In other words, you can convert a horizontal array to a vertical array (and vice versa). Use the TRANSPOSE function to transpose an array.

Consider the following one-dimensional horizontal array constant:

{1,2,3,4,5}

You can enter this array into a vertical range of cells by using the TRANSPOSE function. To do so, select a range of five cells that occupy five rows and one column. Then enter the following formula and press Ctrl+Shift+Enter:

=TRANSPOSE({1,2,3,4,5})

The horizontal array is transposed, and the array elements appear in the vertical range.

Transposing a two-dimensional array works in a similar manner. Figure 17.12 shows a two-dimensional array entered into a range normally and entered into a range by using the TRANSPOSE function. The formula in A1:D3 is

{={1,2,3,4;5,6,7,8;9,10,11,12}}

Figure 17.12. Using the TRANSPOSE function to transpose a rectangular array.

The formula in A6:C9 is

{=TRANSPOSE({1,2,3,4;5,6,7,8;9,10,11,12})}

You can, of course, use the TRANSPOSE function to transpose an array stored in a range. The following formula, for example, uses an array stored in A1:C4 (four rows, three columns). You can enter this array formula into a range that consists of three rows and four columns.

{=TRANSPOSE(A1:C4)}

As you can see in Chapter 18, generating an array of consecutive integers for use in an array formula is often useful. The ROW function, which returns a row number, is ideal for this. Consider the array formula shown here, entered into a vertical range of 12 cells:

{=ROW(1:12)}

This formula generates a 12-element array that contains integers from 1 to 12. To demonstrate, select a range that consists of 12 rows and one column and enter the array formula into the range. You’ll find that the range is filled with 12 consecutive integers (as shown in Figure 17.13).

Figure 17.13. Using an array formula to generate consecutive integers.

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. Excel adjusts the row references so that 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.

Suppose that you have a range of cells that contains text entries (see Figure 17.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 follows and copying it down the column:

=LEN(A1)

Figure 17.14. The goal is to count the number of characters in a range of text.

Then you use a SUM formula to calculate the sum of the values returned by these 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 characters 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}

The array formula is then reduced to:

=SUM({10,9,8,5,6,5,5,10,11,14,6,8,8,7})

The formula returns the sum of the array elements, 112.

If you have values in a range named Data, you can determine the smallest value by using the SMALL function:

=SMALL(Data,1)

You can determine the second smallest and third smallest values by using these formulas:

=SMALL(Data,2)
=SMALL(Data,3)

To add the three smallest values, you could use a formula like this:

=SUM(SMALL(Data,1), SMALL(Data,2), SMALL(Data,3)

This formula works fine, but using an array formula is more efficient. The following array formula returns the sum of the three smallest values in a range named Data:

{=SUM(SMALL(Data,{1,2,3}))}

The formula uses an array constant as the second argument for the SMALL function. This generates a new array, which consists of the three smallest values in the range. This array is then passed to the SUM function, which returns the sum of the values in the new array.

Figure 17.15 shows an example in which the range A1:A10 is named Data. The SMALL function is evaluated three times, each time with a different second argument. The first time, the SMALL function has a second argument of 1, and it returns –5. The second time, the second argument for the SMALL function is 2, and it returns 0 (the second smallest value in the range). The third time, the SMALL function has a second argument of 3 and returns the third smallest value of 2.

Figure 17.15. An array formula returns the sum of the three smallest values in A1:A10.

Therefore, the array that’s passed to the SUM function is

{-5,0,2)

The formula returns the sum of the array (–3).

Suppose that you need to count the number of text cells in a range. The COUNTIF function seems like it might be useful for this task—but it’s not. COUNTIF is useful only if you need to count values in a range that meet some criterion (for example, values greater than 12).

To count the number of text cells in a range, you need an array formula. The following array formula uses the IF function to examine each cell in a range. It then creates a new array (of the same size and dimensions as the original range) that consists of 1s and 0s, depending on whether the cell contains text. This new array is then passed to the SUM function, which returns the sum of the items in the array. The result is a count of the number of text cells in the range.

{=SUM(IF(ISTEXT(A1:D5),1,0))}

Figure 17.16 shows an example of the preceding formula in cell C8. The array created by the IF function is

{0,1,1,1;1,0,0,0;1,0,0,0;1,0,0,0;1,0,0,0}

Figure 17.16. An array formula returns the number of text cells in the range.

Notice that this array contains four rows of three elements (the same dimensions as the range).

A slightly more efficient variation on this formula follows:

{=SUM(ISTEXT(A1:D5)*1)}

This formula eliminates the need for the IF function and takes advantage of the fact that:

TRUE * 1 = 1

and

FALSE * 1 = 0

One key benefit of using an array formula is that you can often eliminate intermediate formulas in your worksheet, which makes your worksheet more compact and eliminates the need to display irrelevant calculations. Figure 17.17 shows a worksheet that contains pre-test and post-test scores for students. Column D contains formulas that calculate the changes between the pre-test and the post-test scores. Cell D17 contains a formula, shown here, that calculates the average of the values in column D:

=AVERAGE(D2:D15)

Figure 17.17. Without an array formula, calculating the average change requires intermediate formulas in column D.

With an array formula, you can eliminate column D. The following array formula calculates the average of the changes but does not require the formulas in column D:

{=AVERAGE(C2:C15-B2:B15)}

How does it work? The formula uses two arrays, the values of which are stored in two ranges (B2:B15 and C2:C15). The formula creates a new array that consists of the differences between each corresponding element in the other arrays. This new array is stored in Excel’s memory, not in a range. The AVERAGE function then uses this new array as its argument and returns the result.

The new array consists of the following elements:

{11,15,-6,1,19,2,0,7,15,1,8,23,21,-11}

The formula, therefore, is reduced to

=AVERAGE({11,15,-6,1,19,2,0,7,15,1,8,23,21,-11})

Excel evaluates the function and displays the results, 7.57.

You can use additional array formulas to calculate other measures for the data in this example. For example, the following array formula returns the largest change (that is, the greatest improvement). This formula returns 23, which represents Linda’s test scores.

{=MAX(C2:C15-B2:B15)}

The following array formula returns the smallest change (that is, the least improvement). This formula returns –11, which represents Nancy’s test scores.

{=MIN(C2:C15-B2:B15)}

If your formula uses a function that requires a range reference, you may be able to replace that range reference with an array constant. This is useful in situations in which the values in the referenced range do not change.

Figure 17.18 shows a worksheet that uses a lookup table to display a word that corresponds to an integer. For example, looking up a value of 9 returns Nine from the lookup table in D1:E10. The formula in cell C1 is

=VLOOKUP(B1,D1:E10,2,FALSE)

Figure 17.18. You can replace the lookup table in D1:E10 with an array constant.

You can use a two-dimensional array in place of the lookup range. The following formula returns the same result as the previous formula, but it does not require the lookup range in D1:E1:

=VLOOKUP(B1,{1,"One";2,"Two";3,"Three";4,"Four";5,"Five";
6,"Six";7,"Seven";8,"Eight";9,"Nine";10,"Ten"},2,FALSE)

This chapter introduced arrays. Chapter 18 explores the topic further and provides some additional examples.