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.