.Net and Office calculate wrong

[FredjeV]

Hi,

 

There is a big problem in how all Microsoft products handles substractions. When you subtract two values the outcome is wrong.

This problem is found in VB6, .NET2001 and VS2005 as well in Excel.

Try this in Excel:

=79,8-84,2 and =79,7-84,1 both should give -4,4 but they give -4,40000000000001 and -4,39999999999999 (in Excel you must show all decimals otherwise it will round and it looks well)

 

In .NET this gives a problem when you compare the values with each other, they are NOT equal and they should be!.

 

This happens in many different cases! so be aware!!

 

Fred

[Deadalus]

It's not a problem, just something every programmer (in any language) should be aware of. It's a consequence of how computers handle decimal numbers.

http://en.wikipedia.org/wiki/Floating_point#Problems_with_floating-point

 

In contexts where such rounding errors are unwanted (like financial calculations), scaled integers can be used, like the Decimal type in VB, decimal in c# and Currency in pre-Net VB(A).

[PlausiblyDamp]

It depends on the data type used, doubles and singles are floating point numbers and as such suffer from rounding errors. It is never a good idea to compare floating poin numbers for exactly this reason.

 

Decimal (under .Net) doesn't exhibit this problem as it uses a fixed point storage.

 

The following will demonstrate both types...

           double d1 = 79.8, d2 = 84,d3;
           d3 = d1 - d2;

           decimal c1 = 79.8m, c2 = 84,c3;
           c3 = c1 - c2;

[snarfblam]

If you want to display a floating point number and make it look pretty, you should use a .ToString overload that supports formatting and rounding. The roundoff error will essentially dissappear.

 

You should never compare doubles for equality. Not only is there a roundoff error, but the accuracy of the calculations is not set in stone. Where in .Net version 1 there may be 15 digits of precision, in .Net version 2 there could be 18, which means that .Net 1 will give you a slightly different result than .Net 2. The best thing to do is have some tolerance when you want to see if two doubles have the same value.

// A difference smaller than this will be considered equality
const tolerance = 0.000001;

double A = 4;
double B = 16.0 / 4.0;
bool isApproximatelyEqual = Math.Abs(A - B) <  tolerance;
MessageBox.Show("A equals B: " + isApproximatelyEqual.ToString());

 

No matter how many people discover this and report it as a bug, the fact will always remain: this is by design.