Microsoft Office Tutorials and References
In Depth Information
Array Formulas
Table 3-2: Excel Error Values
#REF!
The formula refers to a cell that isn’t valid. This can happen if that cell has been deleted
from the worksheet.
#VALUE!
The formula includes an argument or operand of the wrong type. An operand is a value or
cell reference that a formula uses to calculate a result. This error also occurs if your formula
uses a custom VBA worksheet function that contains an error.
#####
A cell displays a series of hash marks under two conditions: The column isn’t wide enough
to display the result, or the formula returns a negative date or time value.
Array Formulas
In Excel terminology, an array is a collection of cells or values that is operated on as a group. An
array formula is a special type of formula that works with arrays. An array formula can produce a
single result, or it can produce multiple results — with each result displayed in a separate cell.
For example, when you multiply a 1 x 5 array by another 1 x 5 array, the result is a third 1 x 5
array. In other words, the result of this kind of operation occupies five cells; each element in the
first array is multiplied by each corresponding element in the second array to create five new
values, each getting its own cell. The array formula that follows multiplies the values in A1:A5 by the
corresponding values in B1:B5. This array formula is entered into five cells simultaneously:
{=A1:A5*B1:B5}
You enter an array formula by pressing Ctrl+Shift+Enter. To remind you that a formula
is an array formula, Excel surrounds it with curly braces in the formula bar. When I
present an array formula in this topic, I enclose it in curly braces to distinguish it from a
normal formula. Don’t enter the braces yourself.
An array formula example
An array formula enables you to perform individual operations on each cell in a range in much
the same way that a programming language’s looping feature enables you to work with elements
of an array. If you’ve never used array formulas before, this section will get your feet wet with a
hands-on example.
Figure 3-6 shows a worksheet with text in A1:A5. The goal of this exercise is to create a single
formula that returns the sum of the total number of characters in the range. Without the single
formula requirement, you’d write a formula with the LEN function, copy it down the column, and
then use the SUM function to add the results of the intermediate formulas.

Search JabSto ::

Custom Search