Using Arrays

Arrays are familiar concepts to computer programmers. Simply defined, an array is a collection of items. Excel is one of the few applications that facilitate array operations, in which items that comprise an array can be individually or collectively addressed in simple mathematical terms. Here is some basic array terminology you should know:

• An array formula acts on two or more sets of values, called array arguments, to return either a single result or multiple results.

• An array range is a block of cells that share a common array formula.

• An array constant is a specially organized list of constant values that you can use as arguments in array formulas.

Arrays perform calculations in a way unlike anything else. You can use them for worksheet security, alarm monitors, linear regression tables, and much more.

One-Dimensional Arrays

The easiest way to learn about arrays is to look at a few examples. For instance, you can calculate the averages shown in Figure 12-32 by entering a single array formula.

Figure 12-32: We entered a single array formula in the selected range F4:F8.

This particular example might be used to help protect the formulas from tampering because modifying individual formulas in cells that are part of an array is impossible. To enter this formula, do the following:

1. Select the range F4:F8.

2. Type the formula in the formula bar, as shown in Figure 12-32.

3. Press Ctrl+Shift+Enter.

The resulting single array formula exists in five cells at once. Although the array formula seems to be five separate formulas, you can't make changes to any one formula without selecting the entire formula-that is, the entire range F4:F8.

You can identify an array formula by looking at the formula bar. If the active cell contains an array formula, the entire formula, including the equal sign, is enclosed in braces-{}-in the formula bar, as you can see in Figure 12-32.

Array Formula Rules

To enter an array formula, first select the cell or range that will contain the results. If the formula produces multiple results, you must select a range the same size and shape as the range or ranges on which you perform your calculations.

Follow these guidelines when entering and working with array formulas:

• Press Ctrl+Shift+Enter to lock in an array formula. Excel will then place a set of curly braces around the formula in the formula bar to indicate that it's an array formula. Don't type the braces; if you do, Excel interprets your entry as text.

• You can't edit, clear, or move individual cells in an array range, and you can't insert or delete cells. You must treat the cells in the array range as a single unit and edit them all at once.

• To edit an array, select the entire array, click the formula bar, and edit the formula. Then press Ctrl+Shift+Enter to lock in the formula.

• To clear an array, select the entire array, and press Delete.

• To select an entire array, click any cell in the array, and press Ctrl+/.

• To move an array range, you must select the entire array and either cut and paste the selection or drag the selection to a new location.

• You can't cut, clear, or edit part of an array, but you can assign different formats to individual cells in the array. You can also copy cells from an array range and paste them in another area of your worksheet.

Two-Dimensional Arrays

In the preceding example, the array formula resulted in a vertical, one-dimensional array. You also can create arrays that include two or more columns and rows, otherwise known as two-dimensional arrays. Figure 12-33 shows an example.

Figure 12-33: We used a two-dimensional array formula in B10:E14 to compute the rank of each exam score. A similar one-dimensional array appears in F10:F14.

To enter a two-dimensional array, do the following:

1. Select a range to contain your array that is the same size and shape as the range you want to use.

2. Type your formula in the formula bar, and press Ctrl+Shift+Enter.

Note 

Unfortunately, you can't create three-dimensional arrays across multiple worksheets in workbooks.

Single-Cell Array Formulas

You can perform calculations on a vast collection of values within a single cell by using an array formula that produces a single value as a result. For example, you can create a simple single-cell array formula to multiply the values in a range of cells by the values in an adjacent range, as shown in Figure 12-34.

Figure 12-34: To calculate total wages paid, we used a single-cell array formula in B3 to multiply hours worked by wages due for each employee individually.

In the example shown in Figure 12-34, you must enter the formula as an array formula (by pressing Ctrl+Shift+Enter); entering it as a regular formula results in a #VALUE error. Our example shows a tiny worksheet, but an array formula like this can make fast work of giant tables.

Using Array Constants

An array constant is a specially organized list of values that you can use as arguments in your array formulas. Array constants can consist of numbers, text, or logical values. Although Excel adds braces for you when you enter array formulas, you must type braces around array constants and separate their elements with commas and semicolons. Commas indicate values in separate columns, and semicolons indicate values in separate rows. The formula in Figure 12-35, for example, performs nine computations in one cell.

Figure 12-35: An array constant is the argument for this array formula.

A Single-Cell Array Formula Application

Suppose you want the total number of items in a table that satisfy two criteria. You want to know how many transactions of more than $1,000 occurred after a specified date. You could add a column to the table containing an IF function to find each transaction that satisfies these criteria and then total the results of that column. A simpler way to do this is to use a single array formula like this one: =SUM((A1:A100>39448)*(C1:C100>999)).

The 39448 in the formula is the serial date value for January 1, 2008. Enter the formula by pressing Ctrl+Shift+Enter. Each item in the first parenthetical expression evaluates to either a 1 (TRUE) or a 0 (FALSE), depending on the date; each item in the second parenthetical expression evaluates also to either a 1 or a 0, depending on whether its value is greater than 999. The formula then multiplies the 1s and 0s, and when both evaluate to TRUE, the resulting value is 1. The SUM function adds the 1s and gives you the total. You can add more criteria by adding more parenthetical elements to the formula; any expression that evaluates to FALSE (0) eliminates that transaction because anything multiplied by 0 is 0.

You could enhance this formula in several ways. For example, replace the serial date number with the DATEVALUE function so you can use "1/1/2008" as an argument instead of having to find the date value yourself. Even better, use cell references as arguments to each element so you can type variable criteria in cells rather than editing the formula. For information about the DATEVALUE function, see Chapter 15, "Formatting and Calculating Date and Time."

To enter a formula using an array constant, follow these steps:

1. Select a range of cells the size you need to contain the result. In Figure 12-35, the argument to the INT function contains three groups (separated by semicolons) of three values (separated by commas), which produces a three-row, three-column range.

2. Enter an equal sign to begin the formula and, optionally, a function name and opening parenthesis.

3. Type the array argument enclosed in braces to indicate that the enclosed values make up an array constant. If you entered a function, type its closing parenthesis.

4. Press Ctrl+Shift+Enter. The resulting array formula contains two sets of curly braces-one set encloses the array constant, and the other encloses the entire array formula.

When entering array constants, remember that commas between array elements place those elements in separate columns, and semicolons between array elements place those elements in separate rows.

Understanding Array Expansion

When you use arrays as arguments in a formula, all your arrays should have the same dimensions. If the dimensions of your array arguments or array ranges do not match, Excel often expands the arguments for you. For example, to multiply all the values in cells A1:B5 by 10, you can use either of the following array formulas: { =A1:B5*10} or {={ 1,2;3,4;5,6;7,8;9,10}*10}.

Note that neither of these two formulas are balanced; ten values are on the left side of the multiplication operator but only one is on the right. Excel expands the second argument to match the size and shape of the first. In the preceding example, the first formula is equivalent to { =A1:B5*{ 10,10;10,10;10,10;10,10;10,10}}, and the second is equivalent to { ={ 1,2;3,4;5,6;7,8;9,10}*{ 10,10;10,10;10,10;10,10;10,10}}.

When you work with two or more sets of multivalue arrays, each set must have the same number of rows as the argument with the greatest number of rows, and each must have the same number of columns as the argument with the greatest number of columns.