Microsoft Office Tutorials and References
In Depth Information
Formula Problems and Solutions
Use the Set Precision as Displayed option with caution, and make sure that you
understand how it works. This setting also affects normal values (nonformulas) that have
been entered into cells. For example, if a cell contains the value 4.68 and is displayed
with no decimal places (that is, 5), selecting the Set Precision as Displayed check box
converts 4.68 to 5.00. This change is permanent, and you can’t restore the original
value if you later clear the Set Precision as Displayed check box. A better approach is
to use Excel’s ROUND function to round the values to the desired number of decimal
places (see Chapter 10). I’ve used Excel for many years, and I’ve never had a need to
use the Set Precision as Displayed option.
Floating-point number errors
Computers, by their very nature, don’t have infinite precision. Excel stores numbers in binary
format by using 8 bytes, which can handle numbers with 15-digit accuracy. Some numbers can’t be
expressed precisely by using 8 bytes, so the number stores as an approximation.
To demonstrate how this limited precision may cause problems, enter the following formula into
cell A1:
=(5.1–5.2)+1
The result should be 0.9. However, if you format the cell to display 15 decimal places, you’ll
discover that Excel calculates the formula with a result of 0.899999999999999. This small error
occurs because the operation in parentheses is performed first, and this intermediate result
stores in binary format by using an approximation. The formula then adds 1 to this value, and the
approximation error is propagated to the final result.
In many cases, this type of error does not present a problem. However, if you need to test the
result of that formula by using a logical operator, it may present a problem. For example, the
following formula (which assumes that the previous formula is in cell A1) returns FALSE:
=A1=.9
One solution to this type of error is to use Excel’s 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
 
Search JabSto ::




Custom Search