Value of zero = 2.44422537765132E-14

[Sinned Rellim]

I have seen this problem described as an Excel bug in the "eggheadcafe"
forum. Quite frankly, however, I can not understand the advanced terminalogy
of the subsequent discussion -- so, I remain at a loss. Here is what I am
doing:

A2-Taxable Sale (numeric entry, two decimals)
B2-Tax Amount (=Round(A2*.08,2))
C2-Sale Total (=Sum(A2+B2)) or Payment Amount (numeric entry, two decimals)
D2-Customer Balance (=Sum(D1+C2))

When the resulting Customer Balance is nil, I often get a multi-digit value
like the example in the Subject line, with zero (0) displayed. Whenever a
dash (-) is displayed, the value is zero (0). So far, I have been unable to
determine why this happens. It seems to be a random occurance. Cell
formatting does not seem to be a factor and subsequent calculations do not
appear to be adversly affected. Nonetheless, inconsistency makes me
suspicious.

Does anyone have an explanation? I am using Excel 2003 SP3. Thanks.

 

[Jerry W. Lewis]

Computers work in binary, not decimal. The only 2-decimal place numbers with
exact binary representations are .00, .25, .50, and .75. All other 2-decimal
place numbers must be approximated, and the approximation is accurate to 15
significant figures. Excel does not display more thant 15 significant
figures, so the approximations are not immediately apparent, but can be
revealed by subtraction as in your example. The math is right, give the
initial unavoidable approximations. The impact can be avoided by changing D2
to =ROUND(D1+C2,2). Microsoft could not do this for you, because it would
not be appropriate for all calculations.

Also note that =Sum(A2+B2) can be simplified to =A2+B2.

Jerry