As an example of what Excel attempts to do, contrast
=1-1/3-2/3
with
=(1-1/3-2/3)
The latter (non-zero) answer is the correct result of the operations
performed on the finite binary approximations to the input numbers.

In the former case, Excel recognizes that the last operation takes the
difference between two numbers that are identical to 15 decimal digits,
and so it assumes that the nozero difference is the result of binary
approximations and arbitrarily zeros the result.

In the latter case, the parentheses prevent Excel from recognizing that
the subtraction between almost equal numbers is the final operation, and
therefore it returns the the nonzero result of the binary operations.

Excel's SUM function applies that logic internally, so that if B1:B3
contain 1, =-1/3, and =-2/3 then both =SUM(B1:B3) and =(SUM(B1:B3)) will
return zero. It is worth noting that both =SUM(1,-1/3,-2/3) and
=(SUM(1,-1/3,-2/3)) return the nonzero binary result; my guess is that
in the function argument (as opposed to in a cell) that Excel is using
the full 10-byte binary representation of these numbers, and thus is not
seeing it in the same way.

The obvious question now is why your case does not result in zero.
Calculate =SUM(A1:A3) and format the result with 15 decimal places, you
will see 0.600000000000001. Excel doesn't return zero for =SUM(A1:A4)
because you have already accumulated too big a binary discrepancy with
SUM(A1:A3).

Jerry

Alex Andronov wrote:

Thanks Niek,

In that document it says: "Excel 97, however, introduced an optimization
that attempts to correct for this problem. Should an addition or subtraction
operation result in a value at or very close to zero, Excel 97 and later will
compensate for any error introduced as a result of converting an operand to
and from binary. The example above when performed in Excel 97 and later
correctly displays 0 or 0.000000000000000E+00 in scientific notation."

I'm running in Excel 2002 but still getting this problem. The number is very
small so I would have thought it would have applied to this opitization.

Thanks for your help though,

Alex.

"Niek Otten" wrote:


Hi Alex,

See

http://support.microsoft.com/default...kb;en-us;78113

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Alex Andronov" <Alex wrote in message
...

I believe I have found an excel bug.

To replicate it put the following simple formulae in excel.

In A1 put: 2.7
In A2 put: -4.3
In A3 put: 2.2
In A4 put: =5.2+(-5.8)

In A6 put: =sum(A1:A4)

If you make A6 show as many decimal places as possible you will suddenly
discover a very small number is being produced. Oddly if you set A4 to
contain -0.6 then it solves the problem.

Any ideas what's going on?

Alex.