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

operators.

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.