Part II: Working with Formulas and Functions
One solution to this type of error is to use the ROUND function. The following formula, for
example, returns TRUE because the comparison is made by using the value in A1 rounded to
one decimal place:
=ROUND(A1,1)=0.9
Here’s another example of a “precision” problem. Try entering the following formula:
=(1.333-1.233)-(1.334-1.234)
This formula should return 0 , but it actually returns -2.22045E-16 (a number very close to
zero).
If that formula is in cell A1, the following formula returns Not Zero:
=IF(A1=0,"Zero","Not Zero")
One way to handle these “very close to zero” rounding errors is to use a formula like this:
=IF(ABS(A1)<1E-6,"Zero","Not Zero")
This formula uses the less-than operator (<) to compare the absolute value of the number
with a very small number. This formula returns Zero.
“Phantom link” errors
You may open a workbook and see a message asking whether you want to update links in
a workbook. This message sometimes appears even when a workbook contains no linked
formulas. Often, these phantom links are created when you copy a worksheet that contains
names.
First, try choosing File ➪ Info ➪ Edit Links to Files to display the Edit Links dialog box.
Then select each link and click Break Link. If that doesn’t solve the problem, this phan-
tom link may be caused by an erroneous name. Choose Formulas ➪ Defined Names ➪ Name
Manager and scroll through the list of names in the Name Manager dialog box. If you see a
name that refers to #REF!, delete the name. The Name Manager dialog box has a Filter but-
ton that lets you filter the names. For example, you can filter the lists to display only the
names with errors.
Using Excel Auditing Tools
Excel includes a number of tools that can help you track down formula errors. This section
describes the auditing tools built into Excel.
Identifying cells of a particular type
The Go to Special dialog box is a handy tool that enables you to locate cells of a particular
type. To display this dialog box, choose Home ➪ Editing ➪ Find & Select ➪ Go to Special.
