Spreadsheet Models for Managers
This reading is especially relevant for Session 4Reference Operators
Formulas in Excel can include operators that combine arguments
to produce intermediate results that are used to evaluate the
formula. You really already know about operators, but you may
be unfamiliar with the use of this word in this way. Examples
of arithmetic operators are +, -, /, and *, which are, respectively,
addition, subtraction, division and multiplication. You know
how these work — let's look at some new ones.
perators do their work on operands.
For example, in the expression "1+3", 1 is the first operand and 3 is the second operand.
Some operators are called "infix"
operators. Examples of infix operators are the arithmetic operators
you're familiar with. Another infix operator is the exponent
operator used for powers of numbers. For example, the formula
4^2 means the same as 42. These operators are called
infix because they sit "in between" their operands.
Some operators are "matchfix" operators.
They come in pairs, like the left and right parentheses. For
example, 2*(4+1) is an operator expression that evaluates to
10. It contains two arithmetic operators, * and +, and two matchfix
operators, "(" and ")" for grouping. Another
matchfix operator is the pair of double quotes. In Excel, double
quote pairs are used to encase character strings. For example
"qwerty" is a character string constant, wrapped in
the string grouping operators, double-quotes.
There are other operators in Excel that you
may not have run into before. For example, the ampersand (&)
joins, or concatenates, two strings together. "qwerty"&"234" evaluates to "qwerty234".
Reference
operators
The three reference operators are the range
operator, the union operator and the intersection operator. They're
represented respectively by the characters colon (range),
comma (union) and space (intersection). All three are infix operators.
The range operator (colon), which is an infix
operator, is used to join two cell references to make a range
reference. The resulting range is the rectangle formed by the
first operand and the second operand. A1:D5
defines the range from A1 to D5.
The range reference A1:A1 isn't illegal — but
it's degenerate. It's exactly the same as A1.
The expression C:C is the (relative)
reference to column C.
There are two other reference operators. They're
also used to combine references in ways that produce new references.
The first is the intersection operator (space),
which is an infix operator. When it's inserted between two references,
it returns the intersection of those two references. The references
can be single cells, ranges, or complex combinations of references
constructed with any of the reference operators. They can also
be the results of any macro or worksheet function that returns a
reference, such as offset or index. The intersection
of two ranges is the set of cells that lie in both ranges. It
can be either a single cell or a (possibly discontiguous) collection
of cells. If two references don't intersect at all, Excel returns
a #NULL! error value. In Figure 1,
the cell C4 is the intersection of
B2:C4 and C4:D7.
The last reference operator is union, represented
by a comma. Like intersection and range, it too is an infix operator
and returns a reference. Just as intersection creates and returns
the intersection of two references, union creates and returns
the union of two references. The union of two references
is the collection of cells that lie in either one (or both) of
the references. For example, (B2:C4,C4:D7)
is a reference to all the cells in either of the two rectangular ranges shaded in Figure 1.
Figure 1
The intersection
and union of two ranges.
Because the union operator is represented by
a comma, which is also used to separate arguments of functions,
you usually have to surround the expression with parentheses
to prevent ambiguous interpretation of the expression by Excel.
But you can use as many commas as you like within one set of
parentheses.
As an exercise, see if you agree that C2:C7=(B2:C4,C4:D7) C:C.
A list of Excel's operators
Believe it or not, Excel has 16 infix operators, 5 matchfix operators, 3 prefix
operators and a postfix operator.
Infix operators
| + |
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 |
| Space |
reference intersection |
| Comma (,) |
reference union |
| Colon (:) |
range reference defined by
two cell references |
| ! |
separate worksheet name from
reference |
Matchfix operators
| Left Side |
Right Side |
Operation |
| " |
" |
string constant |
| { |
} |
array constant |
| ( |
) |
arithmetic grouping or function
arguments or reference grouping |
| ' |
' |
grouping worksheet name |
| [ |
] |
grouping workbook name, or
relative reference in R1C1 style |
Prefix operators
| - |
negative |
| + |
plus |
| $ |
Next component of an A1 reference is absolute |
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.
