Microsoft Office Tutorials and References

In Depth Information

**Chapter 4: Fixing Formula Boo-Boos**

✓
Do the number of open and closed parentheses match?

✓
Does the formula reference the same cell it is entered in? For example

what if cell A1 has this formula: =A1*5. This is called a
circular reference.

This is a bit like a dog chasing its tail.

✓
Does the formula refer to a nonexistent reference?

Each of the problems is handled differently. Excel will offer a fix for

mismatched parentheses but will only
warn
you about formulas that reference

the cell they are entered in. For nonexistent references, Excel will ask you

where to find them. Excel will display an Open File type of dialog box that

you use to browse to the reference, assuming the reference is meant to come

from an external workbook. If a reference to an external workbook was not

the intention, then the dialog box won’t make sense. In this case, dismiss the

dialog box and edit the formula.

Getting parentheses to match

In a mathematical formula, each open parenthesis must have a matching

closing parenthesis. Excel checks your formulas to make sure they comply.

Figure 4-1 shows a simple business calculation that requires parentheses to

make sense. The result is based on multiplying units by price per unit, adding

an additional purchase amount to that, then applying a discount, and finally

applying tax.

Figure 4-1:

Using

parentheses

in a formula.

In math terms, here is how the formula works:

(units sold × price per unit + additional cost) × discount × (1 + tax rate)