Fonction SI / Comportement surprenant
Bonjour,
Je suis tombé sur un comportement étrange de la fonction SI, et comme je ne peux pas l'expliquer, ça m'a bien mis de travers.
J'utilise depuis plusieurs années un classeur Excel, home made, à plusieurs onglets comportant des liens entre eux, et qui me sert à produire des statistiques annuelles. Comme les données sont saisies par au moins 2 personnes, j'ai placé quelques tests de cohérence qui permettent de détecter d'éventuelles anomalies dans les liens et les formules de calcul. Ces tests portent sur des opérations arithmétiques dont le résultat est comparé au résultat attendu, en général zéro. Jusqu'à aujourd'hui, tout allait bien.
En saisissant les dernières données disponibles pour produire les stats habituelles, un warning s'allume.
Après les premières vérif courantes (saisie, valeur infinitésimale d'un résultat de calcul pour un test =0, ordre des soustractions, cellule intermédiaire pour exécuter le test sur cette cellule, ...) j'en arrive à la conclusion que la fonction SI, utilisée dans certaines conditions, produit des résultats surprenants.
=SI(a-b-c=0;VRAI;FAUX) peut renvoyer FAUX dans certains cas même si a-b-c est nul
J'ai alors construit un fichier (que je ne peux pas joindre, la fonction étant indisponible, mais dont j'extrais le tableau ci-dessous) en jouant sur la structure des opérandes : valeur, décimales, nb de digits,...
A | B | C | Test opération = 0 | Opération |
2500.33 | 1500.33 | 1000.00 | VRAI | 0.00 |
12345.67 | 2345.67 | 10000.00 | VRAI | 0.00 |
20000.00 | 10000.00 | 10000.00 | VRAI | 0.00 |
20000.22 | 10000.22 | 10000.00 | FAUX | 0.00 |
22345.67 | 12345.67 | 10000.00 | FAUX | 0.00 |
25000.00 | 15000.00 | 10000.00 | VRAI | 0.00 |
25000.33 | 15000.33 | 10000.00 | FAUX | 0.00 |
25000.44 | 15000.44 | 10000.00 | FAUX | 0.00 |
25123.44 | 15123.44 | 10000.00 | FAUX | 0.00 |
26481.30 | 16381.30 | 10100.00 | VRAI | 0.00 |
26481.32 | 16381.32 | 10100.00 | VRAI | 0.00 |
26481.33 | 16381.33 | 10100.00 | FAUX | 0.00 |
26481.34 | 16381.34 | 10100.00 | VRAI | 0.00 |
26481.35 | 16381.35 | 10100.00 | FAUX | 0.00 |
J'ai rien trouvé, aucune cohérence, aucune logique
Comme je ne suis qu'un amateur intermittent d'Excel, je me suis dit que des professionnels surentrainés pouvaient avoir déjà rencontré ce phénomène et pê avoir trouvé une explication ou même une solution.
Merci d'avance
Bonsoir,
sans rien y connaître on peut voir que c'est un problème de précision d'Excel car pour lui 10000-1000 est égal à 1.818989...E-12 ! soit si je comprend bien : 0.000000000001818989... donc c'est bien différent de 0 !
Si la précision de vos calculs se résume à deux décimales alors vous pouvez écrire ceci :=SI(ARRONDI.INF(G6-H6-I6;2)=0;"vrai";"faux")
Est ce que cela correspondra à votre demande, c'est à vous de le dire !
@ bientôt
LouReeD
Merci @LouReeD
Effectivement la fonction arrondi.inf règle le pb avec 11 décimales mais pas avec 12.
En plus ce résidu d'imprécision dans une soustraction (avec des entrées saisies à la main !) est impossible à afficher dans les codes standards d'affichage d'Excel.
Ce résidu d'imprécision reste aléatoire, et il me semble qu'il n'affecte que les soustractions dans la fonction SI.
Pratiquement mon pb est réglé.
Intellectuellement c'est une autre affaire
Merci @+
Bonjour,
J'avais remarqué cette anomalie sur certaines comparaisons avec la valeur 0. Quand on fait passer un terme de l'autre côté pour éviter ce zéro, il n'y a plus de problème.
Bjr OPTIMIX
Et merci.
La solution est simple, efficace et pertinente.
Comme disait mon prof de maths, c'est une solution "élégante".
Chapeau
C'est bien ce que je pensais, des professionnels surentraînés...
Merci @+
Bonjour,
Pour moi Optimix : idem !
@ bientôt
LouReeD