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:
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.