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.
Search JabSto ::




Custom Search