Excel Advanced Formulas and Functions Training Course

Unit 4:  Array formulas

In this unit, you will learn how to:

• Work with arrays in formulas
• Use the SUMPRODUCT function
• Create elegant formulas that appear to perform spreadsheet magic
• Compare SUMPRODUCT, SUMIFS with SUM arrays
• Use the TRANSPOSE function to switch row and column data

Understanding Array Formulas

If you do any computer programming, you've probably been exposed to the concept of an array. An array is simply a collection of items operated on collectively or individually. In Excel, an array can be one dimensional or two dimensional. These dimensions correspond to rows and columns. For example, a one-dimensional array can be stored in a range that consists of one row (a horizontal array) or one column (a vertical array). A two-dimensional array can be stored in a rectangular range of cells.

As you'll see, arrays need not be stored in cells. You can also work with arrays that exist only in Excel's memory which are known as array constants. You can then use an array formula to manipulate this information and return a result. An array formula can occupy multiple cells or reside in a single cell.

This section presents two array formula examples: an array formula that occupies multiple cells and another array formula that occupies only one cell.

A multicell array formula

The following table 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

After copying the formula down, the worksheet contains sixteen formulas in column D with a sales total formula in D18.

An alternative method uses one formula (an array formula) to calculate all sixteen values in D2:D17. This single formula occupies sixteen cells and returns an array of sixteen values. This is also known as a two dimensional array.

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

1. Select a range to hold the results. In this case, the range is D2:D17. Because you can't display more than one value in a single cell, sixteen cells are required to display the resulting array — so you select sixteen cells to make this array work.

2. Type the following formula:

=B2:B17*C2:C17

3. At this point you would normally press 'Enter' however, because this is an array formula you must press CTRL+SHIFT and then press Enter to activate the formula. This type of formula is also known as a CSE formula (CTRL+SHIFT+ENTER).

Note: You can't insert a multicell array formula into a range that has been designated a table (using Insert > Tables > Table). In addition, you can't convert a range that contains a multicell array formula to a table.

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

{=B2:B17*C2:C17}

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

This formula performs its calculations and returns a sixteen-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:B17 and the values for the second array are stored in C2:C17.

This array formula returns exactly the same values as these sixteen normal formulas entered into individual cells in D2:D17:

=B2*C2
=B3*C3
=B4*C4
=B5*C5
=B6*C6
=B7*C7
=B8*C8
=B9*C9
=B10*C10
=B11*C11
=B12*C12
=B13*C13
=B14*C14
=B15*C15
=B16*C16
=B17*C17

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

• It's a good way to ensure that all formulas in a range are identical.
• Using a multicell array formula makes it less likely that you'll overwrite a formula accidentally. You can't change one cell in a multicell array formula. Excel displays an error message if you attempt to do so.
• Using a multicell array formula will almost certainly prevent novices from tampering with your formulas.

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

• It's impossible to insert a new row into the range. But in some cases, the inability to insert a row is a positive feature. For example, you might not want users to add rows because it would affect other parts of the worksheet.
• If you add new data to the bottom of the range, you need to modify the array formula to accommodate the new data.

Multi-cell array block formula

Here is another example of a muti-cell array that creates the array in a block rather than a single column.  The array formula in range D17:I25 calculates the quantity values in range D6:I14 multiplied by a Unit Price of 200 for Product Codes starting with the letter A. For the other Product Codes the array multiplies by 230.

To create the array formula first highlight the range D17:I25.
Then type the formula:
=IF(LEFT(C6:C14)="a",D6:I14*K1,D6:I14*K2)

Remember to press CSE after typing the formula and make it into an array:
{=IF(LEFT(C6:C14)="a",D6:I14*K1,D6:I14*K2)}
The advantage of using an array here is that there is no need for any absolute referencing of cells because there is no copying involved. To achieve the same calculation without using arrays the formula would read:
=IF(LEFT($C6)="a",D6*$K$1,D6*$K$2)

A single-cell array formula

Now let's take a look at a single-cell array formula, also known as a one dimensional array. The following example which is similar to the previous one does not use column D. The objective is to calculate the sum of the total product sales without using the individual calculations that were in column D.

When you enter this formula, make sure that you use Ctrl+Shift+Enter (and don't type the curly brackets because Excel automatically adds them for you).

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.

Note: In this example, you can use the SUMPRODUCT function to obtain the same result without using an array formula: =SUMPRODUCT(B2:B17,C2:C17)

As you can see, array formulas allow many other types of calculations that are otherwise not possible.

Creating an array constant

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 (separated by commas) into a cell and surround the items with curly brackets as per the following example of a five-item horizontal array constant:

{2,5,0,0,7}

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

=SUM({2,5,0,0,7})

Note: 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 the formula as an array formula will also work producing the same result.

Note: When you specify an array directly (as shown above), you must type in the curly brackets around the array elements. When you enter an array formula, on the other hand, you do not supply the brackets.

Array constant elements

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,”Bob”,”John”,”Sam”}

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%}

Understanding the Dimensions of an Array

As stated previously, an array can be one dimensional or two dimensional. A one-dimensional array's orientation can be horizontal (corresponding to a single row) or vertical (corresponding to a single column).

One-dimensional horizontal arrays

The elements in a one-dimensional horizontal array are separated by commas, and the array can be displayed in a row of cells. The following example is a one-dimensional horizontal array constant:

{1,2,3,4,5}

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

Note: If you enter this array into a horizontal range that consists of more than five cells, the extra cells will contain #N/A (which denotes unavailable values). If you enter this array into a vertical range of cells, only the first item (1) will appear in each cell.

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"}

One-dimensional vertical arrays

The elements in a one-dimensional vertical array are separated by semicolons, and the array can be displayed in a column of cells. The following is a seven-element vertical array constant:

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

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

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

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

{"Bell Housing";"Flange";"Gasket";"Electric Pump 750 amps"}

Two-dimensional arrays

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}

The following figure shows how this array appears when entered into a range (in this case, B3:E5).

If you enter an array into a range that has more cells than array elements, Excel displays #N/A in the extra cells. The next figure shows a 3 × 4 array entered into a 6 × 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.

Naming Array Constants

You can create an array constant, give it a name, and then use this named array in a formula. Technically, a named array is a named formula.

The following figure shows a named array being created from the New Name dialog box. (Access this dialog box by choosing Formulas > Defined Names > Define Name.) The name of the array is Wdays, and it refers to the following array constant:

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

Note: In the New Name dialog box, the array is defined (in the Refers To field) using a leading equal sign (=). Without this equal sign, the array is interpreted as a text string rather than an array. Also, you must type the curly brackets when defining a named array constant; Excel does not enter them for you.

After creating this named array, you can use it in a formula. The next figure shows a worksheet that contains a single array formula entered into the range A1:G1. The formula is:

{=Wdays}

Using TRANPOSE to insert a horizontal array into a vertical range of cells

Because commas separate the array elements, the array has a horizontal orientation. Use semicolons to create a vertical array. Or you can use the Excel TRANSPOSE function to insert a horizontal array into a vertical range of cells. The following array formula, which is entered into a seven-cell vertical range, uses the TRANSPOSE function:

{=TRANSPOSE(Wdays)}

Using INDEX to access individual elements from an array

You also can access individual elements from the array by using the Excel INDEX function. The following formula, for example, returns Wed, the fourth item in the Wdays array:

=INDEX(Wdays,4)

Working with Array Formulas

This section deals with the mechanics of selecting cells that contain arrays and entering and editing array formulas. These procedures differ a bit from working with ordinary ranges and formulas.

Entering an array formula

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.

Selecting an array formula range

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:

• Activate any cell in the array formula range. Display the Go To dialog box (choose Home > Editing > Find & Select > Go To, or just press F5). In the Go To dialog box, click the Special button and then choose the Current Array option. Click OK to close the dialog box.
• Activate any cell in the array formula range and press Ctrl+/ to select the entire array.

Editing an array formula

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 a multicell array formula. If you attempt to do so, Excel displays the message shown in the following figure.

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

• You can't change the contents of any individual cell that makes up an array formula.
• You can't move cells that make up part of an array formula (but you can move an entire array formula).
• You can't delete cells that form part of an array formula (but you can delete an entire array).
• You can't insert new cells into an array range. This rule includes inserting rows or columns that would add new cells to an array range.
• You can't use multicell array formulas inside of a table that was created by choosing Insert > Tables > Table. Similarly, you can't convert a range to a table if the range contains a multicell array formula.

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.

Note: If you accidentally press Ctrl+Enter (instead of Ctrl+Shift+Enter) after editing an array formula, the formula will be entered into each selected cell, but it will no longer be an array formula. And it will probably return an incorrect result. Just reselect the cells, press F2, and then press Ctrl+Shift+Enter.