Microsoft Office Tutorials and References

In Depth Information

**Formula Problems and Solutions**

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 were in cell A1, the following formula would return the string “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 would return Zero.

Phantom link errors

You may open a workbook and see a message like the one shown in Figure 21-5. This message

sometimes appears even when a workbook contains no linked formulas.

Figure 21-5:
Excel’s way of asking whether you want to update links in a workbook.

First, try choosing File

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

➜

Info

➜

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.

➜

Defined Names

➜

These phantom links may be created when you copy a worksheet that contains names.

See Chapter 3 for more information about names.