Part II: Working with Formulas and Functions
A formula can work with both an array constant and an array stored in a range. The follow-
ing formula, for example, returns the sum of the values in A1:D1, each multiplied by the
corresponding element in the array constant:
=SUM((A1:D1*{1,2,3,4}))
This formula is equivalent to the following:
=SUM(A1*1,B1*2,C1*3,D1*4)
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. You can use different types of values in the
same array constant, as in this example:
{1,2,3,TRUE,FALSE,TRUE,"Moe","Larry","Curly"}
An array constant can’t contain formulas, functions, or other arrays. Numeric values can’t
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 one-dimensional or two-dimensional. A one-
dimensional array’s orientation can be horizontal (corresponding to a single row) or vertical
(corresponding to a single column).
One-dimensional horizontal arrays
Each element in a one-dimensional horizontal array is separated by a comma, and the array
can be displayed in a row of cells. If you use a non-English-language version of Excel, your
list separator character may be a semicolon.
The following example is a one-dimensional horizontal array constant:
{1,2,3,4,5}
Displaying this array in a range requires five consecutive cells in a row. To enter this array
into a range, select a range of cells that consists of one row and five columns. Then enter
the following formula and press Ctrl+Shift+Enter:
={1,2,3,4,5}
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.
