Scoop : Excel ne sait pas faire de simple soustraction !

Avez-vous remarqué qu'Excel (ce n'est pas le seul 😰) fait plein d'erreur de calcul !

Certains utilisateurs postent des messages pensant avoir fait la découverte du siècle mais c'est en réalité "normal".

Exemple

=SI( (5,2-5,1) = 0,1 ; "Excel est bon !" ; "Uh, Houston, we've had a problem")

5.2 et 5.1 sont saisie en "dur" pour simplifier l'exemple mais pourraient être des références de cellule contenant ces valeurs.

On obtient (réalisé sans trucage !) :

5,2-5,1 n'est pas égale à 0,1 pour Excel ! 🤔

Et ce n'est pas un cas unique !

D'où vient le problème ?

Les mathématiciens privilégient la notation en fraction pour éviter des erreurs de précision dans leurs calculs.

Par exemple, tout le monde sait que 1/3 c'est environ 0,333333333333 mais pas exactement, il faudrait ajouter des 3 à l'infinie.

Ce problème de précision existe aussi dans d'autre système numérique comme le binaire (Base2 : on compte avec 2 chiffres) mais pour d'autres nombres comme 5.2 et 5.1 par exemple !

5,2 en binaire donne 101, 0011 0011 0011...
5,1 en binaire donne 101, 0 0011 0011 0011...

On voit que 5,2 et 5,1 en binaire sont des nombres périodiques (répétition à l'infinie de la séquence 0011).

Quand Excel convertie 5.2 et 5.1 en binaire pour réaliser le calcul, il a une limite de précision et donc doit faire des arrondies avec ces valeurs d'où une erreur de calcul.

Si ajoute des décimales à l'affichage pour l'opération 5,2-5,1, on obtient :

En 15eme décimale on voit apparaitre l'erreur !

Pas de panique, la plupart du temps ces erreurs ne sont pas gênantes et peuvent être corrigées si nécessaire avec l'utilisation de la fonction ARRONDI.

Cela fait des dizaines d'années que des centaines de millions d'utilisateurs d'Excel y sont confrontés sans trop de problèmes et je rappelle que ce n'est pas qu'Excel qui est "touché" par ce comportement. C'est lié à un choix technologique (calcul à virgule flottante).

En programmation aussi

En VBA

Sub Test()
    If (5.2 - 5.1) = 0.1 Then
        Debug.Print "Ok"
    Else
        Debug.Print "Pas bon"
    End If
End Sub
---
Résultat : "Pas bon"

Remarque :

CDec(5.2)-CDec(5.1) = 0.1 est VRAI
Round((5.2 - 5.1), 1) = 0.1 est VRAI

En Python

if (5.2+5.1-5.2-5.1) == 0 :
   print("Ok")
else:
	 print("Pas bon")
---
Résultat : "Pas bon"

En C

#include <stdio.h>

int main() {
    if ((5.2 - 5.1) == 0.1) {
        printf("Ok\n");
    } else {
        printf("Pas bon\n");
    }
    return 0;
}
---
Résultat : "Pas bon"

Un autre cas étonnant

Dans beaucoup de langage on a :

(5.2+5.1-5.2-5.1) == 0 est égale à FAUX ! (et on se demande pourquoi il y a des bugs)

Cela touche aussi certaine fonction Excel

Fonction MOD (le reste de la division)

=MOD(4,2 ; 0.6) = 0 est égale à FAUX pourtant 6 * 7 =42 donc pas de reste !

Depuis Excel97, on n'est plus concerné par ce cas (ni en VBA), ouf !

Merci pour votre attention bienveillante.