Representing Arrays in Excel 2007 Formulas

An array formula is a special formula that operates on a range of values. If a cell range supplies this range (as is often the case), it is referred to as an array range. If this range is supplied by a list of numerical values, they are known as an array constant. Although this concept may seem foreign, you are really quite familiar with arrays because the column-and-row structure of the Excel worksheet grid organizes your data ranges into one-dimensional and two-dimensional arrays (1-D arrays take up a single row or column, while 2-D arrays take up multiple rows and columns).

The following figure illustrates a couple of two-dimensional arrays with numerical entries of two different sizes. The first array is a 3 x 2 array in the cell range B2:C4. This array is a 3 x 2 array because it occupies three rows and two columns. The second array is a 2 x 3 array in the cell range F2:H3. This array is a 2 x 3 array because it uses two rows and three columns.

Worksheet with two different sizes of arrays.
Worksheet with two different sizes of arrays.

If you were to list the values in the first 3 x 2 array as an array constant in a formula, they would appear as follows:

{1,4;2,5;3,6}

Several things in this list are noteworthy. First, the array constant is enclosed in a pair of braces ({}). Second, columns within each row are separated by commas (,) and rows within the array are separated by semicolons (;). Third, the constants in the array are listed across each row and then down each column, and not down each column and across each row.

The second 2 x 3 array expressed as an array constant appears as follows:

{7,8,9;10,11,12}

Note again that you list the values across each row and then down each column, separating the values in different columns with commas and the values in different rows with a semicolon.

The use of array formulas can reduce the amount of formula copying that you have to do in a worksheet by producing multiple results throughout the array range in a single operation. In addition, array formulas use less computer memory than standard formulas copied in a range. This can be important when creating a large worksheet with many tables because it may mean the difference between fitting all your calculations on one worksheet and having to split your model into several worksheet files.

blog comments powered by Disqus
Advertisement

Inside Dummies.com