Spreadsheet Models for Managers
This reading is especially relevant for Session 2Array Arithmetic
xcel 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 have to 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 array (range).
Operators
that support
array computations
Operator
Symbol |
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.
- 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 (or more) as the other array B, then
Excel behaves as if it internally creates a new array A' that
is 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 (or more), 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
=A1:B3+D1:E3
into the formula bar and press Ctrl+Shift+Enter (Windows or
Mac) or Command+Enter (Mac). On the Mac, the commands Command+Return
or Ctrl+Shift+Enter or Ctrl+Shift+Return also work. The
result that appears in the selected range is the cell-by-cell
sum of the two arrays.
To perform any other arithmetic operation, substitute that operator for
+ in the above formulas. Just as in ordinary arithmetic, you
can use multiple operands if applicable. For example,
=A1:B1+D1:E3+F1:G3
entered as an array formula produces the sum of the three operand arrays.
You can check the behavior of (2) and (3) using the formulas
=A1:A3+D1:E3
and
=A1:B1+D1:E3
Other
array manipulations
As indicated above, the comparison operators also support array computations. For example,
=A1:B3>D1:E3
entered as an array formula produces an array of Boolean values
(TRUE, FALSE) depending on what are
the results of cell-by-cell comparisons of the two arrays. If
we enter the above array formula into the cells G1:H3,
the cell G1 holds the value TRUE
if A1>D1; otherwise it holds FALSE.
(Try it!)
Similarly, string concatenation also works. If we enter as an array formula
=A1:B3&D1:E3
the resulting values will be cell-by-cell concatenations of
the values in the corresponding cells of the operand arrays.
For more about my spreadsheet consulting
and training services, visit SpreadsheetAce.com.
We run our companies, in part, based on spreadsheets, yet we often wonder
if they're really right.
Learn more about improving the reliability and effectiveness of your
financial models.
With dozens of solved problems and special course tools, this course can dramatically increase your
spreadsheet skills. For just USD 199.00 you receive three months of access with unlimited
email support.
Learn more about improving your spreadsheet sophistication and effectiveness.
Identical to the edition you're viewing, but with dozens of solved problems
and special course tools, and
it stands alone — no Internet connection required. For just USD 199.00, it includes
three months of unlimited
email support.
Learn more about improving your spreadsheet sophistication and effectiveness.
f 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.
