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.

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.

>
dummies

Source:http://www.dummies.com/how-to/content/representing-arrays-in-excel-2007-formulas.html