Microsoft Office Tutorials and References
In Depth Information
Creating and Editing Formulas
This example also illustrates a requirement for any concatenation operation: to concatenate
other data types, such as dates and numbers, you must first convert those values to text.
That’s why we used the TEXT function in this formula.
For a more detailed discussion of functions used to convert and manipulate text, see “Text
Functions” on page 370.
Relative, Absolute, and Mixed References
Any formula can contain references to cells. Excel uses the value stored in the referenced
cell to perform any calculations just as though that value had been entered directly in the
formula itself. You can combine ranges of cells for calculations by using three reference
The most common is the range operator, which is a : (colon) between two cell addresses.
It includes the two referenced cells and all cells in between them. In the formula
=SUM(C1:C10), the calculation adds up all values in all cells in the range from C1 to C10.
You can also use a comma as an operator to refer to a discontiguous range. In the formula
=SUM(C1,C3,C5-C10), Excel adds all values in cells C1, C3, and C5 through C10.
The most obscure reference operator of all is the rarely seen intersection operator,
represented by a space. It returns the values from all cells that are common to the ranges on
either side of the space. Thus =SUM(C1:C6 C5:C10) adds the contents of cells C5 and C6,
which are the only ones that both ranges have in common.
Unless you specifically choose otherwise, every reference you enter in a worksheet is
stored as a relative reference. When you move or copy a relative reference from one cell to
another in a worksheet, the reference in the destination cell adjusts to reflect the same
relative location. In Figure 11-5, we copied formulas from cell N7 to the corresponding cell in
each data-containing row below it. We used the Ctrl+` shortcut to show the formula itself
rather than the formula’s results, demonstrating how the row number in each reference
changes to reflect the correct relative location.
In some cases, you want a reference in a formula to point to the cell even when you copy
the formula to a new location. Say you set aside cell A1 as the input cell where you’ll enter
an assumption, such as the annual inflation rate, for use throughout your worksheet. By
trying different values in this cell, you can do what-if exercises with the remainder of the sheet.
To calculate the increase for the value shown in cell D4, you use the formula =D4*(1+A1).
So far so good. When you copy the formula for use with D5, D6, and so on, the first
reference changes just as you want. But so, unfortunately, does the second reference, so you
end up with =D5*(1+A2). Because your assumption is entered only in A1, your formula
returns an incorrect (or at least unexpected) result in each of the other cells.