Excel et sa variance négative...

Aujourd’hui, contrairement aux deux derniers messages, je vous présente un bug de calcul qui – lui – est enfin résolu dans les versions récentes d’Excel.

Il est quand même intéressant de vous le présenter parce qu’il est toujours présent chez tous ceux qui utilisent encore une version XP ou antérieure (98, 97,…) d’Excel.

Définition de la variance

Rappelons que la variance d’une série de valeurs est la moyenne des carrés des écarts entre ces valeurs et la moyenne de la série. Par construction donc, une variance est positive ou nulle. Quand on prend une série de trois nombres consécutifs par exemple n-1, n et n+1, la moyenne est n et la variance est égale à (1^2+0^2+1^2 )/3 = 2/3.

La variance d’une série de valeurs (cf. ci-dessous) est calculée à l’aide de la fonction Var.P(), la fonction Var() étant réservée au calcul de la variance estimée d’une population dont la série serait un échantillon. Voici ce que vous obtenez avec un Excel récent :

Les variances négatives d’Excel

Si vous avez une version d’Excel XP ou antérieure, vous obtenez, avec la même formule, des variances farfelues, dont certaines sont négatives, ce qui est fort de café pour des moyennes de carrés ! Voici ce que j’obtiens avec Excel XP :

Et cela a duré pendant des années et des années, malgré nos remarques – et celles de bien d’autres utilisateurs – adressées à Microsoft… En fait, jusqu’aux versions récentes d’Excel, certaines fonctions statistiques d’Excel étaient non seulement peu précises (au maximum 6 chiffres significatifs, contre 15 au maximum pour Excel), mais elles avaient des bugs !