.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

[szlamany]

Sounds like the evils of floating point math.

I cannot speak for the EXCEL part, but are you using SINGLE or DOUBLE in the VB6 or .Net attempts?

[FredjeV]

Im using doubles (with singles the error is bigger). The strange part of this is when the outcome is 3 or 4 the problem does not exists......

[szlamany]

When you do math on floating point datatypes the outcome is not accurate.

You must use CURRENCY or DECIMAL datatypes if you want accuracy. If you do not need the decimal point then use INTEGER datatypes.

It has to do with how the floating point datatype is stored - and how math is done on it.

We never use SINGLE or DOUBLE.

[mendhak]

I don't know what =79,7-84,1 means but can you post your .NET code? (c0d)

[szlamany]

That is a comma format instead of decimal point format - regional settings...

[mendhak]

Ohh, that European thing always confused me.

[Shaggy Hiker]

There was an excellent thread on this in the VB6 forum....about three years ago. Somebody posted a thorough technical explanation of why this happens. If you wanted to dig for it, you mght find it....or get totally bored.

[yrwyddfa]

Essentially, zero cannot be absolutely represented using the notation that M$ (and many others) use. Sometimes it's zero, othertimes it's very very very close to zero.