Microsoft Office Tutorials and References

In Depth Information

**Chapter 14: Introducing Arrays**

This formula creates a new array (in memory) that consists of the product of the corresponding

elements in the two arrays. The new array is as follows:

{5,12,21,32}

This new array is then used as an argument for the SUM function, which returns the result (70).

The formula is equivalent to the following formula, which doesn’t use arrays:

=SUM(1*5,2*6,3*7,4*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 quotation marks (for example, “Tuesday”). 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 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%}