Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Why not 1*(0.5-0.4-0.1) = 0?
Message
De
06/01/2005 18:41:21
Hilmar Zonneveld
Independent Consultant
Cochabamba, Bolivie
 
 
À
06/01/2005 18:19:49
Cetin Basoz
Engineerica Inc.
Izmir, Turquie
Information générale
Forum:
Visual FoxPro
Catégorie:
COM/DCOM et OLE Automation
Versions des environnements
Database:
MS SQL Server
Divers
Thread ID:
00974881
Message ID:
00974889
Vues:
203
>A friend of mine asked this to me today. I said yes,0 with confidence and he said OK then go and put that in Excel as a formula.
>I thought it was an Excel bug but he turned and said it happens in SQL server with float datatype! I couldn't yet see that myself but trust his experience.
>Any idea and solution?
>TIA
>Cetin

Please note that the difference between 0 and (-2,77555756156289E-17) (which is the result I got in Excel) is insignificant for most practical purposes.

As Tore says, this kind of error is typical for calculations with floating-point numbers. Numbers such as 0.3 are converted to binary, because of the way the numbers are stored.

In some systems, there are alternative methods to represent numbers; I think that would be mainly BCD (binary coded decimal; each half-byte stores one digit). In that case, at least if you do additions and subtractions, no accuracy will be lost. You might want to check whether there is such a datatype in SQL Server, and use it instead of float. Calculations might be slower, but they will be 100% accurate for additions and subtractions (and multiplication, in certain cases, like multiplication with an integer).

The "Currency" data type is another option that should be 100% accurate for additions and subtractions; this is the only such option in Visual FoxPro (up to 4 decimals).

To illustrate what is going on, just try the following: how much is 1/3 + 1/3 + 1/3? The correct answer, of course, is 1. Now, in all the calculations, round the result to 2 decimals: you get 0.33 + 0.33 + 0.33 = 0.99. With more decimals, you get a more accurate result - but you will never get exactly 1.
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform