Ecxel substraction

[Dewolf, Sebastiaan]

Hello,

I am doing some Excel-work recently, and came across this "strange feature"
:

A 87,600000000000000000000000000000
B 89,700000000000000000000000000000
B-A 2,100000000000010000000000000000


Another one:

A
84,7000000000000000000000000000000000000000000000000000000000000000000000000000000000000
B
89,8000000000000000000000000000000000000000000000000000000000000000000000000000000000000
B-A
5,0999999999999900000000000000000000000000000000000000000000000000000000000000000000000


Somebody got an idea why this simple substraction gives a wrong answer? And
even better, how to solve this (not a workaround if possible). I tried it
with 2003 - 2007 versions and both have this ...

kind regards
Sebastiaan

 

[Pete_UK]

Excel uses binary numbers to represent decimal values. Some numbers
cannot be represented to complete accuracy, in the same way that 1/3
or 2/3 cannot be shown exactly as a decimal. So, if you use 0.333 to
represent 1/3, then 3 times 1/3 would give 0.999 and not 1. Excel
gives 15-digit precision, so there is no point in displaying values to
more digits.

For more details look at specification in Excel Help.

Hope this helps.

Pete

 

[Dewolf, Sebastiaan]

Ok, I understand what you are saying

But I dont know what the issue is in my example:
87,6-89,7 <> 2,1

I'm not using thirds or anything? When using Excel, I would expect such a
simple calculation would be ok. Could you perhaps exactly tell me what is
"wrong" with the example?

The problem is that somewhere I substract 2,1; but this does not give me a 0
and therfore gives on my graph a value of 1,...e-14 with a data label
instead of 0


thanks for the help

kind regards
Sebastiaan

Excel uses binary numbers to represent decimal values. Some numbers
cannot be represented to complete accuracy, in the same way that 1/3
or 2/3 cannot be shown exactly as a decimal. So, if you use 0.333 to
represent 1/3, then 3 times 1/3 would give 0.999 and not 1. Excel
gives 15-digit precision, so there is no point in displaying values to
more digits.

For more details look at specification in Excel Help.

Hope this helps.

Pete

 

[Pete_UK]

In the same way that 1/3 cannot be expressed as a decimal, so 1/10
cannot be expressed with complete accuracy in binary - the bits in a
binary fraction represent 1/2, 1/4, 1/8, 1/16, 1/32, 1/64 etc, and it
turns out that 1/10 is a recurring binary fraction. Thus it is
represented only approximately, and occasionally arithmetic will
produce anomalies like you have seen.

Using ROUND in your formula will cater for most of these anomalies,
though, eg:

=ROUND(87,6 - 89,7 ; 3)

will round to 3 decimal places.

Hope this helps.

Pete

 

[Bernard Liengme]

Hi,

Pete has given you a good answer but if you want to learn more please visit
http://support.microsoft.com/kb/42980
"Tutorial to Understand IEEE Floating-Point Errors"
best wishes
Bernard Liengme
Excel MVP