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:

=(B3*B4+B6*B8*(1+B9)).

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

Figure 4-3.

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

Automatic Calculation.

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.