Excel 2010 calculation inexact

Question

Hello Everyone,

It is found 

-------------------

Put 1.01 in one cell (for example in A1). 
1.01*1.01*1.01=1.030301.
However if one calculates formula 
(1) =A1*A1*A1-A1 
(2) =A1*(A1+1)*(A1-1)
(3) =1.030301-A1
(4) =1.030301-1.01

(1),(3),(4) give 0.0203009999999999
(2) gives 0.02030100000000000.

If one uses (2)-(4), then Excel 2010 shows -1.14492E-16.
The calculation can be done as the integer multiplication/subtract and should return the exact result. Only (2) gives the correct answer.

-------------------

Can anyone replicate this? Is it a known bug?

Thanks.

Answer 1

Bonjour,

 

Regarde ici (have a look here) :

 

http://blogs.office.com/b/microsoft-excel/archive/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers.aspx

 

Daniel

 

 

 

Answer 2

Bonjour,

Pouvons-nous considérer que vous avez résolu votre problème? 

N'hésitez pas à revenir et supprimer la réponse marquée si la solution n’est pas correcte. Merci !

Cordialement,

Aurel

---

Aurel BERA, Microsoft
Microsoft propose ce service gratuitement, dans le but d'aider les utilisateurs et d'élargir les connaissances générales liées aux produits et technologies Microsoft. Ce contenu est fourni "tel quel" et il n'implique aucune responsabilité de la part de Microsoft.

Answer 3

Thanks for the response. As end user, I would consider that this problem at least existed since 2008. Why should I care how Microsoft implemented the above calculations? I could use the `scientific calculator` within accessories to get the correct answer however Excel is giving me the wrong answer. Many banks are using spreadsheet to produce the risk reports. ...

Answer 4

It'not an "Excel only" defect, it's linked to floating point

calculation used by every computer system. Read the following page :

 

http://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html

 

Daniel