Array Formulas
An array is simply 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 (because Excel can fit only one value in a 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:
Code:
=A1:A5*B1:B5
NOTE: 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 brackets ({ }) in the
formula bar. Don’t enter the brackets yourself.