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
in a formula.
In math terms, here is how the formula works:
(units sold × price per unit + additional cost) × discount × (1 + tax rate)