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.
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.