Page 360 - Excel 2007 Bible

22_044039 ch17.qxp 11/21/06 11:08 AM Page 317
Alternatively, you can use the SUMPRODUCT function. The formula that follows is not an array formula, but
it uses two array constants.
=SUMPRODUCT({1,2,3,4},{5,6,7,8})
A formula can work with both an array constant and an array stored in a range. The following 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:
=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 quota-
tion 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”} Introducing Array Formulas 17
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 either one dimensional or two dimensional. A one-dimensional array’s
orientation 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 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.
NOTE If you enter this array into a horizontal range that consists of more than five cells, the extra
NOTE
cells will contain #N/A (which denotes unavailable values). If you enter this array into a verti-
cal range of cells, only the first item (1) will appear in each cell.
The following example is another horizontal array; it has seven elements and is made up of text strings:
{“Sun”,”Mon”,”Tue”,”Wed”,”Thu”,”Fri”,”Sat”}
To enter this array, select seven cells in a row and type the following (followed by Ctrl+Shift+Enter):
={“Sun”,”Mon”,”Tue”,”Wed”,”Thu”,”Fri”,”Sat”}

317