Microsoft Office Tutorials and References

In Depth Information

**Using array constants**

Using array constants

An array constant is a specially organized list of values you can use as arguments in your

array formulas. Array constants can consist of numbers, text, or logical values. Although

Excel adds braces for you when you enter array formulas, you must type braces around

array constants and separate their elements with commas and semicolons. Commas

indicate values in separate columns, and semicolons indicate values in separate rows. The

formula in Figure 12-35, for example, performs nine computations in one cell.

Figure 12-35
An array constant is the argument for this array formula.

A single-cell array formula application

Suppose you want the total number of items in a table that satisfy two criteria. You

want to know how many transactions of more than $1,000 occurred after a specified

date. You could add a column to the table containing an IF function to find each

transaction that satisfies these criteria and then total the results of that column. A simpler

way to do this is to use a single array formula like this one:

=SUM((A1:A100>41275)*(C1:C100>999))

The
41275
in the formula is the serial date value for January 1, 2013. Enter the formula

by pressing Ctrl+Shift+Enter. Each item in the first parenthetical expression evaluates to

either a
(TRUE) or a
(FALSE), depending on the date; each item in the second
0

thetical expression evaluates also to either a
or a
, depending on whether its value
0

is greater than 999. The formula then multiplies the 1s and 0s, and when both evaluate

to TRUE, the resulting value is
. The SUM function adds the 1s and gives you the total.
1

You can add more criteria by adding more parenthetical elements to the formula; any

expression that evaluates to FALSE (0) eliminates that transaction because anything

multiplied by 0 is 0.

You could enhance this formula in several ways. For example, replace the serial date

number with the DATEVALUE function so that you can use "1/1/2013" as an argument

instead of having to find the date value yourself. Even better, use cell references as

arguments to each element so that you can type variable criteria in cells rather than

editing the formula. For information about the DATEVALUE function, see Chapter 15,

“Formatting and calculating date and time.”