Excel 2010 Bible

Chapter 32: Making Your Worksheets Error-Free

657

Absolute/relative reference problems

As I describe in Chapter 10, a cell reference can be relative (for example, A1), absolute (for exam-

ple, $A$1), or mixed (for example, $A1 or A$1). The type of cell reference that you use in a for-

mula is relevant only if the formula will be copied to other cells.

A common problem is using a relative reference when you should use an absolute reference. As

shown in Figure 32.3, cell C1 contains a tax rate, which is used in the formulas in column C. The

formula in cell C4 is

=B4+(B4*$C$1)

FIGURE 32.3

Formulas in the range C4:C7 use an absolute reference to cell C1.

Notice that the reference to cell C1 is an absolute reference. When the formula is copied to other

cells in column C, the formula continues to refer to cell C1. If the reference to cell C1 were a rela-

tive reference, the copied formulas would return an incorrect result.

Operator precedence problems

As I describe in Chapter 10, Excel has some straightforward rules about the order in which mathe-

matical operations are performed. When in doubt (or when you simply need to clarify your inten-

tions), you should use parentheses to ensure that operations are performed in the correct order.

For example, the following formula multiplies A1 by A2 and then adds 1 to the result. The multi-

plication is performed first because it has a higher order of precedence.

=1+A1*A2

The following is a clearer version of this formula. The parentheses aren’t necessary, but in this case,

the order of operations is perfectly obvious.

=1+(A1*A2)