Microsoft Office Tutorials and References

In Depth Information

A formula can work with both an array constant and an array stored in a range. The following formula, for ex-

ample, returns the sum of the values in A1:D1, each multiplied by the corresponding element in the array con-

stant:

=SUM((A1:D1*{1,2,3,4}))

This formula is equivalent to

=SUM(A1*1,B1*2,C1*3,D1*4)

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 (for example, “Tuesday”). You can use different types of values in the same array constant, as in this ex-

ample:

{1,2,3,TRUE,FALSE,TRUE,”Moe”,”Larry”,”Curly”}

An array constant cannot contain formulas, functions, or other arrays. Numeric values cannot 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 either one-dimensional or two-dimensional. A one-dimensional array's ori-

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