Excel 2003 calculation bug - subtraction of 1 decimal place

[Jensan]

Hi,

I think I have found a bug on Excel 2003, but I am not sure if it is fixed.
I have written a very simple "program" in a new clean workbook:

A1 : 30.2
B1 : 32.3
C1 : 1.1
D1 : 1
E1 : =A1-B1+C1+D1

The result is 2.22045E-15..........I suppose it should be zero.
The numbers are so simple, and the formula is straight forward enough that I
don't think it is ignorable. I understand that using ROUND function or check
the option "precision as display" can solve the problem, but is it a little
bit too much to do for such simple work?

Could anyone provide me the bug fix number (if any), or any reference on
this?

Thank you very much.

Amazing Bug.

Jensan

 

[Bob Phillips]

It is not a bug, it is floating point arithmetic,
http://en.wikipedia.org/wiki/IEEE_754

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Gord Dibben]

=A1-B1+(C1+D1) returns 0


Gord Dibben MS Excel MVP

 

[Jerry W. Lewis]

Excel (and almost all other software) does binary math (as described in Bob's
link). In binary, most terminating decimal fractions are non-terminating
binary fractions that can only be approximated (just as 1/3 can only be
approximated as a decimal fraction). When your inputs are only approximately
what you intended, it should be no surprise when the output is also only an
approximation.

IEEE double precision gives at least 15 figure accuracy in its approximation
to floating point numbers. That means that your problem is effectively
30.2000000000000???
-32.3000000000000???
+1.10000000000000??
+1
--------------------------
00.0000000000000???
which is what you got, since 2.22045E-15 is beyond the 15-figure precision
of your input numbers.

Specifically, the binary approximations have decimal values of
30.199999999999999289457264239899814128875732421875
-32.2999999999999971578290569595992565155029296875
+1.100000000000000088817841970012523233890533447265625
+1

 

[Jensan]

Thanks a lot for all your replies.

So, do you mean that there is no way to ensure the calculation is exactly
correct except using the ROUND function?

This formula is one of the most basic ones, and it could happens anytime but
you may not have noticed this.
Perhaps using different displaying formats can help in some cases, but it
does not help any more if there is any checking included. For example in my
case, I add a checking at the end like this:
F1: IF(E1=0)
I will get a FALSE value then.

Any suggestion on this?

Thank you for your attention.

 

[Jerry W. Lewis]

With finite precision arithmetic (even if binary conversions were not
involved), you cannot prevent cancellation of accuracy due to subtraction or
division. If the only subtraction/division is the last operation, then you
can easily predict how much accuracy will be lost. Other operation orders
may lose less accuracy, but predicting the amount of accuracy left is not
trivial in general.

As a result, you should avoid tests like A=B, and instead use
ABS(A-B)<epsilon, where epsilon is an appropriate tolerance for the
particular calculation. It is not possible to define a general epsilon that
will work for every situation. However, if you are only adding and
subtractin numbers with no data beyond the nth decimal place, then you could
use epsilon = 0.5*10^(-n), or equivalently ask whether ROUND(A-B,n)=0.

Jerry

 

[Jensan]

Thank you.

With finite precision arithmetic (even if binary conversions were not
involved), you cannot prevent cancellation of accuracy due to subtraction or
division. If the only subtraction/division is the last operation, then you
can easily predict how much accuracy will be lost. Other operation orders
may lose less accuracy, but predicting the amount of accuracy left is not
trivial in general.

As a result, you should avoid tests like A=B, and instead use
ABS(A-B)<epsilon, where epsilon is an appropriate tolerance for the
particular calculation. It is not possible to define a general epsilon that
will work for every situation. However, if you are only adding and
subtractin numbers with no data beyond the nth decimal place, then you could
use epsilon = 0.5*10^(-n), or equivalently ask whether ROUND(A-B,n)=0.

Jerry

 

[Daniel Paul]

Hi Jensan,
I'm not a .NET Developer. But i noticed it is there in excel 2007 also. One another thing that might help debugging it might be the fact that because there is a switching of signs, it is showing the error

A1-B1 is negative then you are adding two positive values to make it zero. if you just pull "-B1" to the end it will be fixed for now.

I think microsoft should look into this. this is too silly a mistake which will be overlooked. Though it wont matter unless used in precision calculations that multiplies the value by several millions, it is quite unprofessional from microsoft part.

Regards,

Daniel



Jensa wrote:

Excel 2003 calculation bug - subtraction of 1 decimal place
01-Aug-07

Hi

I think I have found a bug on Excel 2003, but I am not sure if it is fixed
I have written a very simple "program" in a new clean workbook

A1 : 30.
B1 : 32.
C1 : 1.
D1 :
E1 : =A1-B1+C1+D

The result is 2.22045E-15..........I suppose it should be zero
The numbers are so simple, and the formula is straight forward enough that I
don't think it is ignorable. I understand that using ROUND function or check
the option "precision as display" can solve the problem, but is it a little
bit too much to do for such simple work

Could anyone provide me the bug fix number (if any), or any reference on
this?

Thank you very much

Amazing Bug

Jensan

EggHeadCafe - Software Developer Portal of Choice
Sum of Numbers Captcha: Keeping it Simple
http://www.eggheadcafe.com/tutorial...83-8eb1c526ca7f/sum-of-numbers-captcha-k.aspx

 

[Niek Otten]

Read this:

http://support.microsoft.com/kb/78113

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

in message news:[email protected]...