Calculs d'heures (Écarts incompréhensibles)

[Efgé]

Bonjour à tous
Suite à cette question j'ai constaté que les formules utilisées dans ma réponse 7 posaient problème.
Je m'explique
Sur un colonne je récupère des dates et heures avec la formule
=Date + Heure (affichage jj/mm/aa hh:mm:ss)
Sur une autre colonne je suis sensé récupérer la même date sous la forme
=Date précédente + ((1/24)/2) ce qui me donne des demis heures qui se suivent.

Seulement voila: la comparaison des deux résultats ne renvoi pas toujours VRAI

Le fichier exemple joint montre les décalages constatés.

Ma question, Pourquoi? Comment contourner le problème, enfin toutes vos idées et retours seront les bienvenus.

Cordialement

 

[job75]

Re : Calculs d'heures (Écarts incompréhensibles)

Bonjour Efgé,

On sait bien que les calculs sur les heures introduisent des décimales intempestives.

Perso je ne savait pas que leur affichage était en plus assez aléatoire, merci.

Pour compléter le tableau insère une colonne G avec en G2 =ABS(D2-C2)

A+

 

[Modeste geedee]

Re : Calculs d'heures (Écarts incompréhensibles)

Bonsour® Efgé

pour la théorie :
IEEE 754 - Wikipédia

le plus petit temps affichable par excel :
0 0:00:00,001 (1 milliseconde)
s'écrit :
0,00000001157407407407410000000

15 chiffres significatifs
1,15740740740741E-08
mais pour la milliseconde il faut aller jusque la 22éme décimale

pour 0:30 (1/2 heure)
0,0003472222222222220000000000

si l'on travaille par cumul comme dans l'exemple proposé
l'erreur cumulé : 0,0000000000222222220000000000
fini par devenir significative

quant au contournement
j'avoue mon incapacité a définir une règle, dès lors qu'il s'agit d'une comparaison d'égalité numérique ...

 

[JBARBE]

Re : Calculs d'heures (Écarts incompréhensibles)

Bonjour à tous,

Ce que je ne comprends pas c'est que lorsque l'on effectue indépendamment des autres cellules, les calculs (avec les formules) dans d'autres cellules ( exemple :04/05/13 3:30) le résultat affiche "VRAI" !!!

S'agirait-il d'un bug Excel de recopie incrémentée !!!

Bonne journée

 

[Efgé]

Re : Calculs d'heures (Écarts incompréhensibles)

Bonjour Job75, Modeste Geedee, JBARBE
Merci de vos retours.
Donc il faut mettre une croix sur les égalités entre les heures...
Cordialement

 

[JBARBE]

Re : Calculs d'heures (Écarts incompréhensibles)

Je reviens sur la discussion en ayant approfondi mes tests !!!! comme je l'ai dit il s'agit de recopie incrémentée posant problèmes concernant la colonne D avec la formule (=D11+((1/24)/2)) par exemple qui met FAUX en E12!!!

Si l'on met en D11 ceci (04/05/13 04:30:00) donc sans formule la cellule E12 affiche VRAI !

Donc je rejoins modeste concernant cette formule colonne D qui doit mettre le résultat sous nombres décimaux ne pouvant être représentés de manière absolument exacte ( norme IEEE 754) . par exemple, la fraction 1/10 est représentée, sous la forme d'un nombre dont la précision est indéfinie, d'une manière analogue au nombre 1/3 en base décimale ( 0,33333333333.....)

Cette norme limite la précision des nombres à environ 15 chiffres !

Bonne Soirée !

 

[Efgé]

Re : Calculs d'heures (Écarts incompréhensibles)

Re à tous
Merci JBARBE d'avancer sur le sujet
Le problème est, je pense, insoluble puisque, même en mettant les dates-heures "en dur" l'écart d'une demie heure à une autre varie de 0,0208333333284827 à 0,0208333333357587
Dans ces conditions, je ne vois pas comment on peux gérer des heures dans Excel.....
Il faudrait, si cela est possible, trouver une formule d'ajustement, plus ou moins aléatoire.
D'après moi ce n'est pas gagné
Cordialement

 

[JBARBE]

Re : Calculs d'heures (Écarts incompréhensibles)

Rebonsoir,

La seule solution est de mettre en D3 >>>=C2+((1/24)/2) et de recopier vers le bas ( recopie incrémentée ) !!!

Mais est-ce cela que vous voulez ????

Bonne soirée !

 

[Victor21]

Re : Calculs d'heures (Écarts incompréhensibles)

Bonsoir, Fred, Gérard, Ponpon, JBarbe

A toutes fins utiles, ci-joint un test avec le deuxième paramètre de Arrondi() sur les heure.

 

[david84]

Re : Calculs d'heures (Écarts incompréhensibles)

Bonsoir à tous,
comme énoncé précédemment, ce qui pose problème est le report infinitésimal du cumul. Donc si l'on ne reporte que les 1/4 d'heure en ne tenant compte que de la date de départ (donc en fixant D2 et en ajoutant que le 1/4 d'heure voulu) cela ramène VRAI :
remplacer en D2

=D2+((1/24)/2)

par

=D$2+1/48*LIGNE(1:1)

Mais bon, je ne sais pas si cette solution répond à ton problème...
A+

 

[JBARBE]

Re : Calculs d'heures (Écarts incompréhensibles)

Comme je le disais c'est le calcul de la recopie incrémentée dans le même colonne qui est problématique !

Voir exemple ci-joint !

 

[mapomme]

Re : Calculs d'heures (Écarts incompréhensibles)

Bonsoir à tous,

Dans le même ordre d'idée que david84, pour éviter les cumuls des deltas au sein de la colonne D, on pourrait utiliser la formule en D3:

=TEXTE(D2;"jj/mm/aa hh:mm:ss")+1/48

La différence est juste que la formule ne dépend que de la cellule précédente.

 

[mapomme]

Re : Calculs d'heures (Écarts incompréhensibles)

Bonsoir à tous,

Juste une curiosité en partant du fichier v1 et en ne s'intéressant qu'aux 3 premières colonnes (concaténation).

Si en H3, on met la formule : =(41398+B3)=(C3) avec 41398 numéro de série du 04/05/2013,
si en I3, on met la formule : =(B3)=(C3-41398), on s'aperçoit que les formules peuvent donner des résultats différents.

La formule ne comportant que des additions donne toujours VRAI (résultat attendu) tandis que la formule avec soustraction donne tantôt VRAI tantôt FAUX.
Je suis enclin à penser que cela est dû à la convention IEEE 754.

 

[Efgé]

Re : Calculs d'heures (Écarts incompréhensibles)

....

Bonjour Job75, Modeste Geedee, JBARBE, Victor21, David84, mapomme.
Que de monde à mon chevet

Merci à tous.
Dans les propositions, deux sont directement adaptables au souci d'origine.

Celle de David
=D$2+1/48*LIGNE(1:1)

et celle de mapomme
=TEXTE(D2;"jj/mm/aa hh:mm")+1/48

Je pense que la proposition de Victor21 doit l'être également mais pour l'instant je "patouille"

Merci à tous

Cordialement

EDIT Le fichier final est ICI

 

[JBARBE]

Re : Calculs d'heures (Écarts incompréhensibles)

Bonjour,

Avec cette formule en fichier ci-joint et en D7 pour obtenir 04/05/13 2:30>>>=DATE(2013;5;4)+1/9,55 on constate qu'il y a des décimales dans 9,55 et de ce fait la valeur affichée est FAUX !!!

Bonne journée !