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

Custom Search