Finding and Correcting Errors in Calculations

Including calculations in a worksheet gives you valuable answers to questions about your data. As is always true, however, it is possible for errors to creep into your formulas. Excel makes it easy to find the source of errors in your formulas by identifying the cells used in a given calculation and describing any errors that have occurred. The process of examining a worksheet for errors in formulas is referred to as auditing .

Excel identifies errors in several ways. The first way is to fill the cell holding the formula generating the error with an error code . In the following graphic, cell D8 has the error code # NAME ? .

When a cell with an erroneous formula is the active cell, an Error button appears next to it. You can click the button s down arrow to display a menu with options that provide information about the error and offer to help you fix it.

The following table lists the most common error codes and what they mean:

Error Code	Description
#####	The column isn t wide enough to display the value.
#VALUE!	The formula has the wrong type of argument(such as text where a TRUE or FALSE value is required).
#NAME?	The formula contains text that Excel doesn t recognize(such as an unknown named range).
#REF!	The formula refers to a cell that doesn t exist(which can happen whenever cells are deleted).
#DIV/0!	The formula attempts to divide by zero.

Another technique you can use to find the source of formula errors is to ensure that the appropriate cells are providing values for the formula. For example, you might want to calculate the total sales for a product category, but say you accidentally create a formula referring to the products names , not their prices. You can identify that kind of error by having Excel trace a cell s precedents , which are the cells with values used in the active cell s formula. Excel identifies a cell s precedents by drawing a blue tracer arrow from the precedent to the active cell.

You can also audit your worksheet by identifying cells with formulas that use a value from a given cell. For example, you might have the total cost of a single order used in a formula that calculates the average cost of all orders placed on a given day. Cells that use another cell s value in their calculations are known as dependents , meaning that they depend on the value in the other cell to derive their own value. As with tracing precedents, you can point to Formula Auditing on the Tools menu and then click Trace Dependents to have Excel draw blue arrows from the active cell to those cells that have calculations based on that value.

If the cells identified by the tracer arrows aren t the correct cells, you can hide the arrows and correct the formula. To hide the tracer arrows on a worksheet, you point to Formula Auditing on the Tools menu and click Remove All Arrows.

If you prefer to have the elements of a formula error presented as text in a dialog box, you can use the Error Checking dialog box (which you display by clicking Error Checking on the Tools menu) to view the error and the formula in the cell where the error occurs. You can also use the controls in the Error Checking dialog box to move through the formula one step at a time, to choose to ignore the error, or to move to the next or the previous error. If you click the Options button, you can also use the controls in the Options dialog box to change how Excel determines what is an error and what isn t.

Tip  

One change worth noting is that you can have the Error Checking tool ignore formulas that don t use every cell in a region (such as a row or column). If you clear the Formula Omits Cells In Region check box, you can create formulas that don t add up every value in a row or column (or rectangle) without Excel marking them as an error.

For times when you just want to display the results of each step of a formula and don t need the full power of the Error Checking tool, you can use the Evaluate Formula dialog box to move through each element of the formula. To display the Evaluate Formula dialog box, you point to Formula Auditing on the Tools menu and click Evaluate Formula. The Evaluate Formula dialog box is much more useful for examining formulas that don t produce an error but aren t generating the result you expect.

Finally, you can monitor the value in a cell regardless of where in your workbook you are by opening a watch window that displays the value in the cell. For example, if one of your formulas uses values from cells in other worksheets, or even other workbooks, you can set a watch on the cell that contains the formula and then change the values in the other cells. To set a watch, click the cell you want to monitor, point to Formula Auditing on the Tools menu, and then click Show Watch Window. Click Add Watch to have Excel monitor the selected cell.

As soon as you type in the new value, the watch window displays the new result of the formula. When you re done watching the formula, select the watch, click Delete Watch, and close the watch window.

In this exercise, you use the formula auditing capabilities in Excel to identify and correct errors in a formula.

USE the  FindErrors.xls document in the practice file folder for this topic. This practice file is located in the  My Documents\Microsoft Press\Office 2003 SBS\PerformingCalculations folder, and can also be accessed by clicking Start/All Programs/Microsoft Press/Microsoft Office System 2003 Step by Step .

OPEN the  FindErrors.xls document.

1. Click cell D20.

2. On the Tools menu, point to Formula Auditing , and then click Show Watch Window .

3. Click Add Watch, and then click Add in the Add Watch dialog box.

   Cell D20 appears in the watch window.

4. Click cell D8.

   =SUM(C2:C6) appears in the formula bar.

5. On the Tools menu, point to Formula Auditing , and then click Trace Precedents .

   A blue arrow appears between cell D8 and the group of cells from C2 to C6,indicating that cells in the C2:C6 range are precedents of the value in cell D8.

   

6. On the Tools menu, point to Formula Auditing , and then click Remove All Arrows .

   The arrow disappears.

7. Click cell A1.

8. On the Tools menu, click Error Checking .

   The Error Checking dialog box appears.

9. Click Next .

   The error in cell D8 appears in the Error Checking dialog box.

10. Click the Close button to close the Error Checking dialog box.

11. On the Tools menu, point to Formula Auditing , and then click Trace Error .

    Blue arrows appear, pointing to cell D20 from cells D7 and D15. These arrows indicate that using the values (or lack of values, in this case) in the indicated cells is generating the error in cell D20.

    

12. On the Tools menu, point to Formula Auditing , and then click Remove All Arrows .

    The arrows disappear.

13. In the formula bar, delete the existing formula, type =AVERAGE(D8,D16) ,and press [ENTER].

    The value $149.08 appears in cell D20.

14. Click cell D20.

15. On the Tools menu, point to Formula Auditing , and then click Evaluate Formula .

    The Evaluate Formula dialog box appears, with the formula from cell D20 displayed.

16. Click Evaluate .

    The result of the formula in cell D20 appears.

17. Click Close .

18. In the watch window, click the watch in the list.

19. Click Delete Watch .

    The watch disappears.

20. On the Tools menu, point to Formula Auditing , and then click Hide Watch Window .

    The watch window disappears.

21. On the Standard toolbar, click the Save button.

    Excel saves your changes.

CLOSE the  FindErrors.xls document.