Microsoft Office Tutorials and References
In Depth Information
Chapter 4: Fixing Formula Boo-Boos
Don’t be hasty! The correction proposed by Excel will correct the
mismatched parentheses but will not create the correct formula. Look
closely at the following example of a proposed correction by Excel:
But what you really need is this: =(B3*B4+B6)*B8*(1+B9).
Excel simply added the missing parenthesis to the end of the formula. A good
idea, but not good enough. If the proposed correction were accepted, a result
of $549.13 would be returned in this example. The correct answer is $268.46.
In this case, you should reject the proposal and fix the formula yourself.
Do not assume Excel’s proposed formula corrections are right for you.
Carefully review the proposed correction and accept or reject accordingly.
Avoiding circular references
A circular reference occurs when a cell refers to itself, whether directly or
indirectly. For example, if =100 + A2 is entered into cell A2, then a direct
circular reference has been created. An indirect circular reference is when
the formula in a given cell refers to one or more other cells that in return
refer back to the original cell. For example a formula in A1 refers to cell A2,
A2 refers to A3, and A3 refers back to A1.
Figure 4-3 shows a worksheet that has a direct circular reference. Cell D10 is
meant to sum the values above it but mistakenly includes itself in the sum:
=SUM(D4:D10). Excel reports the problem in the message box shown in
If Automatic Calculation is turned off, then the circular reference is
unnoticed until a manual calc is done (by pressing F9) or the setting is changed to
When the dialog box in Figure 4-3 appears, you have a few choices:
✓ Clicking OK lets the formula entry complete, but the result is not
correct. In fact, you may just end up with a zero.
✓ Clicking Help takes you to the Help system Circular Reference topic.
Figure 4-4 shows the Formulas tab in the Excel Options dialog box. Here is
where the calculation setting — automatic or manual — is set. Note that the
Iteration check box is here as well. When this is set, circular references are
allowed. How they calculate values in this case is dependent on the Maximum
Iterations and Maximum Change settings.