Excel 2010 Bible

Part IV: Using Advanced Excel Features

660

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.220446E-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 like the one shown in Figure 32.5. 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.

FIGURE 32.5

Excel’s way of asking whether you want to update links in a workbook.

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 phantom link may be

caused by an erroneous name. Choose Formulas ➪ Defined Names ➪ Name Manager and scroll

through the list of names. If you see a name that refers to #REF!, delete the name. The Name

Manager dialog box has a Filter button that lets you filter the names. For example, you can filter

the lists to display only the names with errors.