Microsoft Office Tutorials and References

In Depth Information

**Formula Problems and Solutions**

Pay attention to the colors

When you edit a cell that contains a formula, Excel color-codes the cell and range references in

the formula. Excel also outlines the cells and ranges used in the formula by using corresponding

colors. Therefore, you can see at a glance the cells that are used in the formula.

You also can manipulate the colored outline to change the cell or range reference. To change the

references that are used in the formula, drag the outline’s border or
fill handle
(at the lower-right

corner of the outline). Using this technique is often easier than editing the formula.

Absolute/relative reference problems

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

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

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

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

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

formula in cell C4 is as follows:

=B4+(B4*$C$1)

Figure 21-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

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

Operator precedence problems

Excel has some straightforward rules about the order in which mathematical operations are

performed in a formula. In Table 21-1, operations with a lower precedence number are performed

before operations with a higher precedence number. This table, for example, shows that

multiplication has a higher precedence than addition. Therefore, multiplication is performed first.