Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

As stated previously, an array can be either one-dimensional or two-dimensional. A one- dimensional array's orientation can be either vertical or horizontal.

One-Dimensional Horizontal Arrays

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

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 one row and then type the following (followed by pressing 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. 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 single 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 pressing 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";"Do-Dads";"Thing-A-Majigs"}

To enter this array into a range, select four cells in a column, enter the following formula and then press Ctrl+Shift+Enter:

={"Widgets";"Sprockets";"Do-Dads";"Thing-A-Majigs"}

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 x 4 array constant:

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

To display 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 pressing Ctrl+Shift+Enter:

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

Figure 14-2 shows how this array appears when entered into a range (in this case, B3:E5).

Figure 14-2: A 3 x 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 14-3 shows a 3 x 4 array entered into a 10 x 5 cell range.

Figure 14-3: A 3 x 4 array, entered into a 10 x 5 cell range.

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

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

Excel will not allow you to enter a formula that contains an invalid array.