Recapping the Array Formula
To review how the array formula counts the values for the modal category of Ford, consider the following:
The formula’s purpose is to count the number of instances of the modal category, Ford, whose name is in cell C1.
The
innermost fragment in the formula, A2:A21=C1, returns an array of 20
TRUE or FALSE values, depending on whether each of the 20 cells in
A2:A21 contains the same value as is found in cell C1.
The
IF() function examines the TRUE/FALSE array and returns another array
that contains 1’s where the TRUE/FALSE array contains TRUE, and 0’s
where the TRUE/FALSE array contains FALSE.
The
SUM() function totals the values in the array of 1’s and 0’s. The
result is the number of cells in A2:A21 that contain the value in cell
C1, which is the modal value for A2:A21.
Using an Array Formula
Various reasons exist for using array formulas in
Excel. Two of the most typical reasons are to support a function that
requires it and to enable a function to work on more than just one
value.
Accommodating a Function
One reason you might need to use an array formula is
that you’re employing a function that must be array-entered if it is to
return results properly. For example, the FREQUENCY() function, which
counts the number of values between a lower bound and an upper bound requires that you enter it in an array
formula. Another function that requires array-entry is the LINEST()
function.
Both
FREQUENCY() and LINEST(), along with a number of other functions,
return an array of values to the worksheet. You need to accommodate
that array. To do so, begin by selecting a range of cells that has the
number of rows and columns needed to show the function’s results.
(Knowing how many rows and columns to select depends on your knowledge
of the function and your experience with it.) Then you enter the
formula that calls the function by means of Ctrl+Shift+Enter instead of
simply Enter; again, this sequence is called array entering the formula.
Accommodating a Function’s Arguments
Sometimes you use an array formula because it
employs a function that usually takes a single value as an argument,
but you want to supply it with an array of values. The example in cell
C2 of Figure 2
shows the IF() function, which usually expects a single condition as
its first argument, accepting an array of TRUE and FALSE values as its
first argument:
=SUM(IF(A2:A21=C1,1,0))
Typically, the IF() function deals with only one
value as its first argument. For example, suppose you want cell C2 to
show the value “Current” if cell A1 contains the value 2010; otherwise,
B1 should show the value “Past”. You could put this formula in B1,
entered normally with the Enter key:
=IF(A1=2010,“Current”,“Past”)
You can enter that formula normally, via the Enter
key, because you’re handing off just one value, 2010, to IF() as its
first argument.
However, the example concerning the number of instances of the mode value is this:
=SUM(IF(A2:A21=C1,1,0))
The first argument to IF() in this case is an array
of TRUE and FALSE values. To signal Excel that you are supplying an
array rather than a single value as the first argument to IF(), you
enter the formula using Ctrl+Shift+Enter, instead of the Enter key
alone as you usually would for a normal Excel formula or value.
Looking Inside a Formula
Excel has a couple of tools that come in handy from
time to time when a formula isn’t working exactly as you expect—or when
you’re just interested in peeking inside to see what’s going on. In
each case you can pull out a fragment of a formula to see what it does,
in isolation from the remainder of the formula.
