Perte de précision dans une somme

[Loïc]

Bonjour,

J'ai une longue liste de montants à additionner.
Arrivé à environ 2 700 montants, Excel dérape et me donne un résultat légèrement faux : voir l'exemple dans le fichier joint.

Certes, ce n'est qu'un millionième, mais cette petite erreur peut ensuite en engendrer d'autres plus importantes.

Je n'ai pourtant pas l'impression de dépasser la limite de précision d'Excel (15 chiffres)...

Qui pourrait me dire pourquoi j'ai ce problème et surtout comment l'éviter ?

Merci d'avance,
Loïc [file name=Pb calcul_20050404154102.zip size=50389]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/Pb calcul_20050404154102.zip[/file]

 

[dg62]

Bonjour Loic


Comment as-tu vérifié ton calcul ?

 

[Loïc]

Salut Didier,

Tous les montants sommés ont au maximum 2 décimales.
J'ai même fait un arrondi à 2 décimales de chaque montant pour être sûr qu'il n'y avait pas un millionième qui se promenait.

Et pourtant, Excel (j'ai la version 2002 PC) finit par me sortir
455024649.149999 au lieu de 455024649.15

Si tu regardes le fichier joint, tu comprendras de suite.

Encore merci pour ton aide,
Loïc

 

[Ti_]

C'est effectivement un problème que j'avais déjà signalé il y a quelque temps sur l'ancien forum. Il est manifeste que les résultats renvoyés sont faux sur ces poussières de décimales. Pour t'affranchir de ces erreurs au millionnième ou plus, le plus simple est d'arrondir également le résultat retourné au nombre de décimales effectivement utilisé.
Il vaut mieux donc bien garder à l'esprit ces décimales intempestives qui peuvent provoquer des effets de bord en faussant certains calculs qu'on voudrait très précis

 

[JC de Lorient]

salut le forum

surprenant!!!
moi j'ai bien ,15 en décimal avec format /nombre/2 décimal

JC

 

[Loïc]

Salut JC de Lorient ,

Oui, moi aussi, si je met un format à 2 décimales à la cellule résultat, il va m'afficher ,15, mais la valeur en mémoire reste ,149999 et c'est cette dernière qui sera utilisée pour des calculs ultérieurs...

Merci beaucoup à Ti_ pour sa solution.

Mais je continue à me demander pourquoi Excel dérape ainsi...

 

[_Thierry]

Bonjour Ti, Loïc, JC, Didier, le Forum

Moi aussi j'ai eu souvent ce genre de problèmes alors que je ne veux gérer que des arrondis à deux décimales...

En fait je ne sais pas si Loïc fait dans le Scientifique, mais moi je suis plutôt coté Banque/Investissement, et la précision nécessaire me permet de tourner ON une option d'Excel de base applicable au WorkBook (qui est irrémédiable une fois appliquée, donc Attention avant de Tester, faire un BackUp) :

En VBA
ActiveWorkbook.PrecisionAsDisplayed = True

Manuellement
Menu Excel
[li]=> Outils[/li]
[li]=> Options[/li]
[li]=> Onglet 'Calculation'[/li]
[li]=> Dans le Frame 'WorkBook Option' cocher 'Precision As Displayed'[/li]

Maintenant ceci me convient et m'évite bien des soucis sur des 'erreurs d'additions', mais donc tout dépend des besoins attendus...

Bonne Fin de Journée
@+Thierry

EDITION !!!

Evidemment j'ai oublié de dire que j'avais testé avec succès cette méthodfe sur le classeur de Loïc... Arf tout le temps dérangé au Bureau LOL !!!

Message édité par: _Thierry, à: 04/04/2005 16:47

 

[Loïc]

Re:Merci !!!

Salut Thierry,

Merci beaucoup pour ta solution. C'est vrai qu'elle marche sur mon fichier exemple.
Elle est quand même 'risquée', par exemple si on a des affichages en milliers d'EUR, les sommes faites sur ces données risquent d'être fausses à leur tour...

Bon, mais au moins, je sais maintenant que ce n'est pas mon ordi qui déconne ni moi qui ai abusé de quelque substance...


Bonne soirée à tous et encore merci pour vos contributions,
Loïc

 

[_Thierry]

Re Loïc et le Fil

Non, le 'PrecisionAsDisplayed' ne modifiera pas les 'Milliers' enfin je ne pense pas, car sinon, je pense que j'aurais eu des soucis depuis Excel 5 où j'applique cette Option !

Non en fait c'est très simple, quand tu fais un colonne de Subtotaux comme dans ton cas, alors que dans la colonne adjacente qui contient des valeurs à deux décimales (même si ces valeurs proviennent d'autres formules avec un arrondi), Excel dans un élan de je ne sais quel zèle, va 's'amuser' en prendre en comptre des millionnièmes et même plus... alors que l'on affiche que deux décimales par le Format de la colonne 'Cumul'...

Dans ce cas là, si on n'a pas besoin de calculer sur les millionnièmes, alors cette Option est Valable, elle va calculer uniquement les valeur que l'on voit.

On aura
10 / 3 = 3.33 Cumul =     3.33
10 / 3 = 3.33 Cumul =    6.66
10 / 3 = 3.33 Cumul =    9.99

et Pas ! :
10 / 3 = 3.33 Cumul =    3.33
10 / 3 = 3.33 Cumul =    6.67
10 / 3 = 3.33 Cumul = 10.00

Par contre le Danger pour lequel je mettais en garde c'est que l'on perdra la valeur de 10 / 3 = 3.33333333333333 si on applique cette option de l'affichage si dessus à deux décimale, c'est surtout embêtant pour les Calculs Scientifiques, de Géométrie etc... Pas pour les calculs financiers...

Bonne Fin de Journée
@+Thierry

Message édité par: _Thierry, à: 04/04/2005 17:35

 

[Loïc]

Reerte de précision dans une somme : ça persiste

Re-salut Thierry,

Ben chez moi, ce n'est pas très concluant :
Si tu reprends mon fichier exemple, et que tu fais une somme en bas de la colonne A, même après avoir fais 'precision as Displayed', la somme reste à #.149999.

Je l'ai fait dans le fichier joint pour ceusses qui nous prennent en route.

Merci quand même,
Loïc [file name=Pb calcul_20050404181628.zip size=22705]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/Pb calcul_20050404181628.zip[/file]

 

[Abel]

Reerte de précision dans une somme : ça persiste

Bonjour le fil,

Je déraille peut être (réminiscences du 1er avril ?) mais il me semble me souvenir d'un fil sur le forum 1ère génération où il avait été question de l'importance des parenthèses dans les calculs.
Pas seulement pour les priorités d'opérations mais aussi de leurs effets sur la précisions de calculs dans certaines conditions.
Il devait y avoir Thierry ou Michel ou David ou nos filles (vi, c'est les notres hi hi ...) dans ce fil.

Ma mémoire fonctionne encore, ou bien ? :silly:

Abel.

 

[andré]

Reerte de précision dans une somme : ça persiste

Salut à vous tous, plus spécialement les anciens qu'il est toujours bon de retrover à leur poste ( Ti & +@)!

Vous me semblez tous oublier la limite de précision de 15 chiffres !
Et pourtant la derni§re fois que j'ai eu ce problème, c'est Ti qui me l'a très justement rappelé (petit programme qui calcule la monnaie pour plusieurs paiements à effectuer).

Trois principes importants : ARRONDIR, ARRONDIR et ARRONDIR !

Bonne soirée.
Ândré.

 

[_Thierry]

Reerte de précision dans une somme : ça persiste

Bonsoir Cher Ami Ândré, Abel et Re à Tous

Oui ça tout à fait d'accord Ândré, mais dans mon petit tableau que je me suis 'rongé les ongles' à mettre en forme sur ce Forum pour l'alignement... Le PrecisionAsDisplay me suffit, je n'ai pas d'erreur. C'est tout ce que je voulais tenter de montrer à Loïc qui semble-t'il n'a pas bien compris avec son dernier Post...

Pour Loïc, si tu as '455024649,1499993 et que tu appliques cette option PrecisionAsDisplay ... et bien tu auras la même chose... et pas '455024649,15', puisque c'est le 'Display' que tu as choisis...

Fais mon test avec la division de 10 / 3 = 3,33 en format Numérique 2 décimales... avec 3 lignes et sous 'toto'... tu comprendras mieux...

Mais sinon oui, même en VBA, on a accès direct à Round sans même passer par une WorkSheetFunction... Et je ne me gène pas de l'utiliser pour éviter certaines surprises dans nos comptes en banques... LOL

Bonne Nuit à Tous et surtout aux Belges buveurs de Duvel !!!
@+Thierry

 

[andré]

Reerte de précision dans une somme : ça persiste

Salut +@Thierry,

'Se ronger les onglets' ... MDR !

Je ne la connaissais pas et il faut bien être belge pour la trouver, surtout lorsqu'on rencontre un problème sur XL !

La dernière expression bruxelloise que je viens d'apprendre :
Du boudin noir (bloedworst en néerlandais, bloodpanch en bruxellois) est la viande la moins chère.
Donc chez le boucher dans le quartier des marolles (vieux quartier populaire de Bruxelles), lorsqu'il y a du monde dans le magasin, on évitera de faire usage de ce nom, mais on commandera plutôt : 'Donnez-moi six côtelettes du Congo'.

Ândré.