Page 122 - Excel Workbook for Dummies

11_798452 ch06.qxp 3/13/06 7:48 PM Page 105
Chapter 6: Copying and Correcting Formulas 105
The moment you close this alert dialog box, the floating Circular Reference tool-
bar is displayed only to be overshadowed immediately by a Microsoft Help
dialog box. This particular Help dialog box gives you information either on locat-
ing and removing a circular reference or on making a circular reference work by
changing the number of times that the program evaluates it formula.
2. Click the Make a Circular Reference Work by Changing the Number of Times
Microsoft Excel Iterates Formulas link to display its information in the Help
dialog box. Look over this information or print it out before you close the Help
dialog box.
Excel draws arrows that point to the cells involved in this circular reference,
starting with cell B15 that computes the bonuses and then going to the formula
in cell B17 that computes the total other income, and from there to cell B19 that
computes total income (or loss) before taxes, and, finally, from there to cell B21
that computes the net income.
These computations constitute a circular reference because the formula in cell
B15 computes the amount of the bonuses as a percentage of the net income in
cell B21 (=B21*20%), which, in turn, is dependent upon the amount of the bonus
as it is included in the other income calculation in cell B17 that is then fed to the
total income (loss) calculation in cell B19, which is itself used in calculating the
net income in cell B21.
3. Select the Iteration check box on the Calculation tab of the Options dialog box
(Tools➪Options).
The moment you select OK to close the Options dialog box and to put this new
Calculation setting into effect, Excel resolves the circular reference in cell B15
that calculates the bonuses based on the net income and the Circular Reference
toolbar. All the arrows showing all the cells involved in this circular reference
disappear from the worksheet.
4. Save this version of the Income Statement with the filename Solved6-7.xls in
your Chapter 6 folder inside the My Practice Spreadsheets folder on your hard
disk and then close this file.