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.

Hi Alex,

I believe I have found an excel bug.
no, this is normal. It' got to do with how the numbers are stored in
binary format and transferred back.

just use a normal number format format to avoid this. This could give
you results like "-0", then use the round-function for correct display.

arno

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.

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.

Alex Andronov wrote:
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."

And you believe online help? That's somewhat unfair: note the keyword
'attempts'.

What it means is that if you enter the formula

=2.7-4.3+2.2+5.2-5.8

it does evaluate to zero. However, if these values are in different
cells, all bets are off. Interestingly, if you change the order of the
expressions so that the 5.2-5.8 expression doesn't come last, Excel
will return 0.

Getting back to the main point, use ROUND if you want a rounded result.
In this case, =ROUND(SUM(yourrange),6) would return 0.

So, are you saying that NASA should not be using Excel?

Biff

"Harlan Grove" wrote in message
ups.com...
Alex Andronov wrote:
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."

And you believe online help? That's somewhat unfair: note the keyword
'attempts'.

What it means is that if you enter the formula

=2.7-4.3+2.2+5.2-5.8

it does evaluate to zero. However, if these values are in different
cells, all bets are off. Interestingly, if you change the order of the
expressions so that the 5.2-5.8 expression doesn't come last, Excel
will return 0.

Getting back to the main point, use ROUND if you want a rounded result.
In this case, =ROUND(SUM(yourrange),6) would return 0.

Hi Biff,

Interesting thought. Suppose you're NASA and you're aiming at the moon. You
may be off .0000000000000th of a %. The moon's diameter is...etc.
But then, if you aim at a planet and miss the surface, not the centre, by
such a %, and there is an atmosphere, you may well get burned.
It all depends on the application's need. In general, an error of
onehundredbilliardth part of a promille is not shocking.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Biff" wrote in message
...
So, are you saying that NASA should not be using Excel?

Biff

"Harlan Grove" wrote in message
ups.com...
Alex Andronov wrote:
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."

And you believe online help? That's somewhat unfair: note the keyword
'attempts'.

What it means is that if you enter the formula

=2.7-4.3+2.2+5.2-5.8

it does evaluate to zero. However, if these values are in different
cells, all bets are off. Interestingly, if you change the order of the
expressions so that the 5.2-5.8 expression doesn't come last, Excel
will return 0.

Getting back to the main point, use ROUND if you want a rounded result.
In this case, =ROUND(SUM(yourrange),6) would return 0.

Just another thought would be if one was expecting the sum to be 0, then
=ROUNDUP(A6,0) would also return 0. But it returns 1! Oops.

The small number that one sees (8.88178E-16) is the same as:
=POWER(2,-50)

Others have pointed out that during a calculation, Excel will carry these
small errors till the end, then try to correct. Ie Sum(...)
Sometimes if you break the calculation up, Excel may be able to round these
errors earlier. For example, both:
=(A1+A2)+(A3+A4)
and
=A1+(A2+A3+A4)
return 0

Interesting subject of course...
--
Dana DeLouis
Win XP & Office 2003


"Niek Otten" wrote in message
...
Hi Biff,

Interesting thought. Suppose you're NASA and you're aiming at the moon.
You may be off .0000000000000th of a %. The moon's diameter is...etc.
But then, if you aim at a planet and miss the surface, not the centre, by
such a %, and there is an atmosphere, you may well get burned.
It all depends on the application's need. In general, an error of
onehundredbilliardth part of a promille is not shocking.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Biff" wrote in message
...
So, are you saying that NASA should not be using Excel?

Biff

"Harlan Grove" wrote in message
ups.com...
Alex Andronov wrote:
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."

And you believe online help? That's somewhat unfair: note the keyword
'attempts'.

What it means is that if you enter the formula

=2.7-4.3+2.2+5.2-5.8

it does evaluate to zero. However, if these values are in different
cells, all bets are off. Interestingly, if you change the order of the
expressions so that the 5.2-5.8 expression doesn't come last, Excel
will return 0.

Getting back to the main point, use ROUND if you want a rounded result.
In this case, =ROUND(SUM(yourrange),6) would return 0.

"Dana DeLouis" wrote...
....
Sometimes if you break the calculation up, Excel may be able to round these
errors earlier. For example, both:
=(A1+A2)+(A3+A4)
and
=A1+(A2+A3+A4)
return 0
....

The reason is that finite precision floating point addition isn't
associative.

"Biff" wrote...
So, are you saying that NASA should not be using Excel?
....

Darn straight! Slide rules got us to the moon & back!

It would be great if had (at least the choice for) scaled integer
arithmetic, like in some programming languages. Maybe it could be applied
automatically if cells are formatted as currency.
Anyway, it could be done better than it's done now, indeed not at all the
way we think about numbers.
I've put it on my wishlist to MS, but I'm not too optimistic.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel


"Harlan Grove" wrote in message
...
"Dana DeLouis" wrote...
...
Sometimes if you break the calculation up, Excel may be able to round
these
errors earlier. For example, both:
=(A1+A2)+(A3+A4)
and
=A1+(A2+A3+A4)
return 0
...

The reason is that finite precision floating point addition isn't
associative.

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.