Window provides precise information on the location of the cell being

watched and the formula in the cell. For example, the first watched cell is

on cell D8 in sheet “Date to serial” in the file “Date and Time.xls.” The

formula in the cell is “=DATE(F7, E7, D7)”.

79

Statistical Analysis with Excel

Figure 77: You can add many cells to the Watch Window

ERROR CHECKING AND FORMULA EVALUATOR

5.5

(ONLY AVAILABLE IN THE XP VERSION OF EXCEL)

The tools are accessed through TOOLS/ERROR CHECKING and

TOOLS/FORMULA AUDITING/EVALUATE FORMULA.

The Error Checking dialog shows the formula in the cell as well as the

type of error. In this example, these are “=DEGREE(COS(C6))” and

“Invalid Name Error,” respectively.

The button (“Help on this error”) links to a help file containing assistance

on understanding and debugging the error.

The button “Show Calculation Steps” links to a step-by-step debugger that

assists in catching the calculation step at which the error occurred.

This debugger has the same functionality as the Formula Auditor

(accessed through TOOLS/FORMULA AUDITING/EVALUATE

FORMULA).

80

Chapter 5: Tracing Cell References & Debugging Formula Errors

Figure 78: The Error Checking dialog shows the formula in the cell as well as the type of error

The button “Ignore Error” keeps the error “as is.” The button Options

opens the dialog for setting error-checking options. The choices within the

dialog are listed in section 5.8.

The Formula Evaluator shows the step at which the first calculation error

occurred. This helps in identifying the primary problem. In this example,

no error has occurred in the formula part “COS(C6))”. The dialog informs

you that “The next evaluation (that is, calculation step), will result in an

error.”

Figure 79: The Formula Evaluator shows the step at which the first calculation error occurred

81

Statistical Analysis with Excel

After clicking on evaluate, you see that the error is in the formula part

“DEGREE.” Excel also informs you of the type of error” “#NAME?”

suggests that “DEGREE” does not match the name of any Excel function.

(The correct function is “DEGREES.”)

The “COS“ function is nested within the DEGREE function. Clicking on

“Step In” will evaluate the nested function only.

Figure 80: After clicking on evaluate...

The “COS“ function is evaluated. The function has no error.

If a function has more than two levels of nesting, then you can use the

“Step Out” button to evaluate the function at the higher level of

nesting.

82

Chapter 5: Tracing Cell References & Debugging Formula Errors

Figure 81: The “COS“ function is evaluated

FORMULA AUDITING MODE (ONLY AVAILABLE IN

5.6

THE XP VERSION OF EXCEL)

This feature is accessed through TOOLS/FORMULA

AUDITING/FORMULA AUDITING MODE. After this mode is selected,

when you select a cell that has or is referenced by a formula, Excel

highlights the other referenced/referencing cells.

In addition, you have quick access (via the “Formula Auditing” toolbar) to

all the Auditing tools discussed earlier in this chapter.

Figure 82: Formula Auditing Mode

83

Statistical Analysis with Excel

CELL-SPECIFIC ERROR CHECKING AND

5.7

DEBUGGING

On every cell whose value evaluates to an error value, you will see a small

icon with a “!” image and a downward arrow. Click on the arrow to obtain

assistance for debugging the error.