Spreadsheet Models for Managers
Getting Access to Spreadsheet Models for Managers
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. |
| Order "Spreadsheet Models for Managers, downloadable hyperbook edition" 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:
|
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.
Spreadsheet Models for Managers
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.
- Operators that support array computations
- The fundamentals of array arithmetic
- Entering array formulas
- Other array manipulations
- Misuse of array formulas
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
|
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.
- 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.
- 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).
- 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).
- 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. |
| Order "Spreadsheet Models for Managers, downloadable hyperbook edition" 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
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).
Last Modified: Wednesday, 27-Apr-2016 04:15:26 EDT
For many of you, matrix multiplication and array arithmetic are new ideas. It’s easy to get lost in the details of how they work and then forget about why we use them.
To keep a clear view of the forest and avoid focusing only on the trees, remember why we use matrix multiplication and array arithmetic. Briefly, we use them because we find that it’s very often helpful to decompose a problem into parts (analysis), then do calculations on the parts, and finally reassemble the final solution from the results of those partial calculations (synthesis).
Matrix multiplication and array arithmetic provide us with very convenient methods for performing those intermediate calculations on the parts. They’re the tools that make analysis and synthesis so powerful.