Worksheet Calculation

When you change the value in any of the cells to which a formula refers, Excel updates the displayed values of the formula as well. This updating process is called recalculation, and it affects only those cells containing references to cells that have changed. By default, Excel recalculates whenever you make changes to a cell. If a large number of cells must be recalculated, the word Calculating appears in the status bar, along with a percentage of progress meter if the process is going to take a particularly long time. You can interrupt the recalculation process simply by doing something, such as using commands or making cell entries; Excel pauses and then resumes recalculation when you finish.

INSIDE OUT Legacy Recalc

When you open an Excel 2010 workbook, Excel recalculates only those formulas that depend on cell values that have changed. However, because of changes in the way Excel 2010 recalculates, when you open a workbook that was saved in an older Excel file format, Excel recalculates all the formulas in the workbook each time you open it. To avoid this, save the workbook in the Excel Workbook (.xlsx or .xlsm) file format.

Recalculating Manually

image with no caption

There are various reasons why you might want to prevent Excel from recalculating each time you enter data in a cell, but computer speed, which used to be one of the top reasons, really isn’t much of an issue anymore (unless your computer is very old). Nevertheless, you can switch from automatic to manual recalculation, and Excel will recalculate only when you tell it to. To set manual recalculation, click the Calculation Options button on the Formulas tab on the ribbon, and then choose the Manual option. You can also click the File tab, Options, and then select the Formulas category to display the additional options shown in Figure 12-27.

Here are a few facts to remember about calculation options:

  • With worksheet recalculation set to manual, the status bar displays the word Calculate if you make a change; click it to initiate recalculation immediately.

  • The Recalculate Workbook Before Saving check box helps make sure the most current values are stored on disk.

  • To turn off automatic recalculation only for data tables, select the Automatic Except For Data Tables option. For more information, see Using Data Tables on page 635.

    The Formulas category in the Options dialog box controls worksheet calculation and iteration.

    Figure 12-27. The Formulas category in the Options dialog box controls worksheet calculation and iteration.

  • image with no caption

    To recalculate all open workbooks, click the Calculate Now button in the Calculation group on the Formulas tab, or press F9.

  • image with no caption

    To calculate only the active worksheet in a workbook, click the Calculate Sheet button in the Calculation group on the Formulas tab, or press Shift+F9.

Calculating Part of a Formula

You might want to see the result of just one part of a complex formula if, for example, you are tracking down a discrepancy. To change only part of a formula to a value, select the part you want to change, and press F9. You also can use this technique to change selected cell references in formulas to their values. Figure 12-28 shows an example.

If you’re just verifying your figures, press the Esc key to discard the edited formula. Otherwise, if you press Enter, you replace the selected portion of the formula.

Select any part of a formula, and press F9 to convert it to its resulting value.

Figure 12-28. Select any part of a formula, and press F9 to convert it to its resulting value.

Note

You can also click the Evaluate Formula button on the Formulas tab to troubleshoot your workbook models. For more information, see Evaluating and Auditing Formulas on page 263.

Working with Circular References

A circular reference is a formula that depends on its own value. The most obvious type is a formula that contains a reference to the same cell in which it’s entered. For example, if you type =C1-A1 in cell A1, Excel displays the error message shown in Figure 12-29. After you click OK, Excel opens the Help dialog box, which displays a pertinent topic.

This error message appears when you attempt to enter a formula that contains a circular reference.

Figure 12-29. This error message appears when you attempt to enter a formula that contains a circular reference.

If a circular reference warning surprises you, this usually means you made an error in a formula. If the error isn’t obvious, verify the cells that the formula refers to by using the built-in formula-auditing features. For details, see Auditing and Documenting Worksheets on page 261.

When a circular reference is present in the current worksheet, the status bar displays the text Circular References followed by the cell address, indicating the location of the circular reference on the current worksheet. If Circular References appears without a cell address, the circular reference is located on another worksheet.

As you can see in Figure 12-30, when you click the arrow next to the Error Checking button on the Formulas tab and click Circular References, any circular references that exist on the current worksheet are listed on a menu (which appears only if a circular reference is present). Click the reference listed on this menu to activate the offending cell.

The Circular References menu appears if any circular references are present.

Figure 12-30. The Circular References menu appears if any circular references are present.

You can resolve many circular references. Some circular formulas are useful or even essential, such as the set of circular references shown in Figure 12-31. These formulas are circular because the formula in cell N30 depends on the value in N31, and the formula in N31 depends on the value in N30.

Figure 12-31 illustrates a circular reference scenario called convergence: The difference between results decreases with each iterative calculation. In the opposite process, called divergence, the difference between results increases with each calculation.

When Excel detects a circular reference, tracer arrows appear on the worksheet. To draw additional arrows to track down the source of an unintentional circular reference, select the offending cell, and then click Trace Precedents on the Formulas tab to draw tracer arrows to the next level of precedent cells, as shown in Figure 12-31.

Note

For more information about tracer arrows and other auditing features, see Auditing and Documenting Worksheets on page 261.

Note

You’ll find the Circular Reference.xlsx file with the other examples on the companion Web site.

The discount formula in cell N30 is circular because it depends on the total, which in turn depends on the discount value in N30.

Figure 12-31. The discount formula in cell N30 is circular because it depends on the total, which in turn depends on the discount value in N30.

After you dismiss the error message shown in Figure 12-29, the formula will not resolve until you allow Excel to recalculate in controlled steps. To do so, click the File tab, Options, Formulas category, and in the Calculation Options section, select the Enable Iterative Calculation check box. Excel recalculates all the cells in any open worksheets that contain a circular reference.

If necessary, the recalculation repeats the number of times specified in the Maximum Iterations box (100 is the default). Each time Excel recalculates the formulas, the results in the cells get closer to the correct values. If necessary, Excel continues until the difference between iterations is less than the number typed in the Maximum Change text box (0.001 is the default). Thus, using the default settings, Excel recalculates either a maximum of 100 times or until the values change less than 0.001 between iterations, whichever comes first.

If the word Calculate appears in the status bar after the iterations are finished, more iterations are possible. You can accept the current result, increase the number of iterations, or lower the Maximum Change threshold. Excel does not repeat the “Cannot Resolve Circular Reference” error message if it fails to resolve the reference. You must determine when the answer is close enough. Excel can perform iterations in seconds, but in complex circular situations, you might want to set the Calculation option to Manual; otherwise, Excel recalculates the circular references every time you make a cell entry.

Note

The Solver add-in, a “what-if” analysis tool, offers more control and precision when working with complex iterative calculations. For details, see Using the Solver on page 655.

Understanding the Precision of Numeric Values

Here are three interesting facts about numeric precision in Excel:

  • Excel stores numbers with as much as 15-digit accuracy and converts any digits after the 15th to zeros.

  • Excel drops any digits after the fifteenth in a decimal fraction.

  • Excel uses scientific notation to display numbers that are too long for their cells.

TROUBLESHOOTING

Rounded values in my worksheet don’t add up.

Your worksheet can appear erroneous if you use rounded values. For example, if you use cell formatting to display numbers in currency format with two decimal places, Excel displays the value 10.006 as the rounded value $10.01. If you add 10.006 and 10.006, the correct result is 20.012. If all these numbers are formatted as currency, however, the worksheet displays the rounded values $10.01 and $10.01, and the rounded value of the result is $20.01. The result is correct, as far as rounding goes, but its appearance might be unacceptable for a particular purpose, such as a presentation or an audit.

You can correct this problem by changing the currency format, or you can click the File tab, Options, Advanced category. In the section entitled When Calculating This Workbook, select the Set Precision As Displayed check box. However, you should select this check box only with extreme caution because it permanently changes the underlying values in your worksheet to their displayed values. For example, if a cell containing the value 10.006 is formatted as currency, selecting the Set Precision As Displayed check box permanently changes the value to 10.01. For more information, see Formatting Numbers on page 324.

Table 12-3 contains examples of how Excel treats integers and decimal fractions longer than 15 digits when they are typed in cells with the default column width of 8.43 characters.

Table 12-3. Examples of Numeric Precision

Typed Entry

Displayed Value

Stored Value

123456789012345678

1.23457E+17

123456789012345000

1.23456789012345678

1.234568

1.23456789012345

1234567890.12345678

1234567890

1234567890.12345

123456789012345.678

1.23457E+14

123456789012345

Excel can calculate positive values as large as 9.99E+307 and approximately as small as 1.00E–307. If a formula results in a value outside this range, Excel stores the number as text and assigns a #NUM! error value to the formula cell.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset