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 * 5 array by another 1 * 5 array, the result is a third 1 * 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:

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 braces in the formula bar. When I present an array formula in this book, I enclose it in curly braces to distinguish it from a normal formula. Don't enter the braces yourself.

0 0

Post a comment