Page 86 - Excel Data Analysis

05 537547 Ch04.qxd 3/4/03 11:52 AM Page 72

EXCEL DATA ANALYSIS

TRACE A FORMULA ERROR

hen you create formulas within a worksheet to Typically an error occurs when the formula refers to an
analyze data, Excel evaluates all arguments of the invalid cell value. For example, if the cell contains the
W formula and returns a value. If Excel cannot formula =A1/A2 and cell A2 contains the number 0, Excel
properly evaluate the formula, it displays one of the default returns the error message #DIV/0!, which indicates that
error messages in the formula cell. Because most formula the formula attempted to divide by zero. See Appendix D
errors are a result of values in other cells, Excel lets you for a description of the Excel error messages.
trace the error. Excel's Trace Error feature visually steps you If you are unable to spot the cause of the formula error
through which formulas reference which cells. When you with the trace arrows, you can edit the cell to display the
select this feature, Excel draws blue arrow lines from the color-coding of the formula arguments to aid in locating the
formula cell to each cell that the formula references. If a cell. If the formula contains a function, as you modify the
referenced cell contains a formula and that formula also formula, Excel displays the formula syntax under the cell.
contains an error, Excel draws a red line between the You simply make the desired modifications to the formula
formula cells. To view the cells that a second formula cells to eliminate the display of the error. See the section
references, you can select the Trace Cells option again. By "Edit Formulas" for more information on editing the
seeing which cells your formulas reference, you can often arguments of a formula.
spot the cell value that produces the formula error.

TRACE A FORMULA ERROR

⁄ Click the cell containing ¤ Click Tools ➪ Formula ■ Excel creates arrows ‹ Double-click the formula
the formula error. Auditing ➪ Trace Error. between the argument cells cell with the mouse.
and the formula cell.
■ An Error button displays
next to the formula error cell
when you select it

72