Chapter 10: Introducing Formulas and Functions
221
l (^) If Excel is in the middle of a lengthy calculation, it temporarily suspends the calculation
when you need to perform other worksheet tasks; it resumes calculating when you’re fin-
ished with your other worksheet tasks.
l Formulas are evaluated in a natural sequence. In other words, if a formula in cell D12 depends
on the result of a formula in cell D11, Excel calculates cell D11 before calculating D12.
Sometimes, however, you may want to control when Excel calculates formulas. For example, if you
create a worksheet with thousands of complex formulas, you’ll find that processing can slow to a
snail’s pace while Excel does its thing. In such a case, set Excel’s calculation mode to Manual —
which you can do by choosing Formulas ➪ Calculation ➪ Calculation Options ➪ Manual (see
Figure 10.15).
FIGURE 10.15
You can control when Excel calculates formulas.
You can sometimes use a circular reference to your advantage. For example, suppose your company
has a policy of contributing 5 percent of its net profit to charity. The contribution itself, however, is
considered an expense — and is therefore subtracted from the net profit figure. This produces a circular
reference (see the accompanying figure).
The Contributions cell contains the following formula:
=5%*Net_Profit
The Net Profit cell contains the following formula:
=Gross_Income-Expenses-Contributions
These formulas produce a resolvable circular reference. If the Enable Iterative Calculation setting is on,
Excel keeps calculating until the Contributions value is, indeed, 5 percent of Net Profit. In other
words, the result becomes increasingly accurate until it converges on the final solution.
Intentional Circular References