  If you use Excel to model businesses, business processes, or business transactions, this course will change your life. You’ll learn how to create tools for yourself that will amaze even you. Unrestricted use of this material is available in two ways.

As a stand-alone Web site
It resides on your computer, and you can use it anywhere. No need for Internet access.
At this Web site
If you have access to the Internet whenever you want to view this material, you can purchase on-line access. Unlimited usage. I’m constantly making improvements and you’ll get them as soon as they’re available.

To Order On Line

 Order "Spreadsheet Models for Managers, on-line edition, one month" by credit card, for USD 69.95 each, using our secure server, and receive download instructions by return email.
 Order "Spreadsheet Models for Managers, on-line edition, three months" by credit card, for USD 199.00 each, using our secure server, and receive download instructions by return email.

To Order by Mail

 Make your check payable to Chaco Canyon Consulting, for the amount indicated: For the download: USD 199.00 For access online for three months: USD 199.00 For access online for one month: USD 69.95 And send it to: Chaco Canyon Consulting 700 Huron Avenue, Suite 19C Cambridge, MA 02138

To use the course software you’ll need some other applications, which you very probably already have. By placing your order, you’re confirming that you have the software you need, as described on this site. # This reading is especially relevant for Session 2Array Arithmetic

Excel can perform operations — including arithmetic — on arrays of cells. Using this capability, you can perform complex computations with minimal effort. But of course, to use it, you must understand it.

An array in Excel is just a rectangular range of cells. You can perform any arithmetic operation on arrays, including addition, subtraction, multiplication, and division. You can also exponentiate — raise one array to the power of another array. Referring to the complete list of Excel’s operators, the table below shows the list of operators that support array computations.

In all of what follows, when an array (range) has 3 rows and 2 columns, we call it a “3x2 array” or “3x2 range”. An array or range with m rows and n columns is said to be an m x n or mxn array (range).

## Operators that support array computations

 OperatorSymbol Operation + addition - subtraction * multiplication / division ^ exponentiation > is greater than < is less than = is equal to >= is greater than or equal to <= is less than or equal to <> is not equal to & concatenation of strings % percentage postfix operator - negative prefix operator

Before looking at the more exotic of these operators, let’s first understand how array arithmetic works.

## The fundamentals of array arithmetic

Let’s start by looking at addition.

1. The sum of two arrays, A and B, that are the same size and shape, is an array of cells C of the same size and shape, in which each cell is the sum of the two corresponding cells in the two arrays A and B.
2. If one of the arrays, say A, has only one row, but it has the same number of columns as the other array B, then Excel behaves as if it internally creates a new array A' that’s the same size and shape as B. A' is created by replicating enough of the single row of A as needed to match B, as many times as necessary to match the number of rows of B. Then Excel applies (1) to compute C. If B is the array that has only one row, then B is extended by replicating that row to match the number of rows of A, and then C is computed by applying (1).
3. If one of the arrays, say A, has only one column, but has the same number of rows as B, then Excel behaves as if it internally creates a new array A' that’s the same size and shape as B. A' is created by replicating enough of the single column of A as needed to match B, as many times as necessary to match the number of columns of B. Then Excel applies (1) to compute C. If B is the array that has only one column, then B is extended by replicating that column to match the number of columns of A, and then C is computed by applying (1).
4. If the arrays aren’t the same size and shape, and if neither (2) nor (3) are applicable, Excel computes the sum for the parts of the arrays that do correspond (starting at the upper left corners of each, and fills in #N/A for the rest of the cells.

## Entering array formulas

Let’s say that the array Aarray is in A1:B3, and that the array Barray is in D1:E3. The arrays are the same size and shape, 3x2, so the result will be 3x2. To compute the array sum of these two arrays, select a 3x2 range, say G1:H3. Then enter the formula

<snip>… Some of the page you’ve been reading has been suppressed from this sampler. To see the complete version of this article, why not order the full course? It’s available at this Web site or in downloadable format.

 Order "Spreadsheet Models for Managers, on-line edition, three months" by credit card, for USD 199.00 each, using our secure server, and receive download instructions by return email.

…<end snip>

Similarly, string concatenation also works. If we enter as an array formula

=A1:B3&D1:E3

the resulting values are cell-by-cell concatenations of the values in the corresponding cells of the operand arrays.

Many worksheet functions, but not all, also support array computations. To determine which ones do and which don’t, you must perform experiments. We know of no place where this property is actually documented.

## Misuse of array formulas

A common misuse of array formulas is what we call degenerate array formulas. A degenerate array formula is a formula entered into a single cell, and which always yields the same result as the identical formula entered into that same cell, but as a non-array formula. For example, {=SUM(A1:A7)} entered into cell A8 is a degenerate array formula, because it always yields the same result as =SUM(A1:A7).