Une déclaration faite par un abonné ExcelTips (Chuck) en réponse à une question Help Wanted a provoqué des commentaires intéressants de la part de nombreux autres abonnés ExcelTips. Chuck a déclaré que, statistiquement, la valeur 0,5 devrait être arrondie à la moitié du temps et à la baisse l’autre moitié du temps, car elle se situe exactement au milieu de deux valeurs entières. L’analogie a été fournie que si une balle de tennis était équilibrée sur un filet, statistiquement la balle devrait tomber à gauche la moitié du temps et à droite l’autre moitié du temps.

Il semble que de telles déclarations suscitent des sentiments forts, même parmi d’autres statisticiens. (Toutes les disciplines semblent avoir leurs diverses guerres de religion où les sentiments sont élevés.) Comme l’a mentionné un correspondant, c’est «le vieux problème« clôtures contre poteaux de clôture »dans le comptage des intervalles entre les nombres». L’argument est de savoir où quelque chose «tombera» quand il est situé directement sur un poteau de clôture. Le problème avec l’analogie balle de tennis et filet (ou clôtures et poteaux de clôture) est que le filet au milieu du court n’est pas la seule ligne de séparation précise.

Par exemple, disons que l’extrémité gauche d’un court de tennis a une ligne marquée «4.0» et l’autre extrémité une ligne marquée «5.0». Cela signifie que le réseau est marqué « 4.5 ». Bien qu’une balle de tennis puisse s’équilibrer sur la marque 4,5 et tomber dans les deux sens, théoriquement, la balle pourrait également s’équilibrer sur la ligne à chaque extrémité du court (4,0 et 5,0) et tomber dans les deux sens.

Un correspondant a exprimé le sentiment que l’arrondissement de 0,5 à la hausse ou à la baisse (moitié dans un sens et moitié dans l’autre) n’est pas approprié car il introduit un biais dans les données. Considérez la situation où vous avez affaire à un chiffre à droite de la virgule décimale: vous avez les nombres 7,0, 7,1, 7,2, etc., tout au long de 7,9. Lors de l’arrondissement de ces chiffres, cinq valeurs arrondiraient toujours vers le bas (7,0 à 7,4), une valeur pourrait arrondir dans les deux sens (7,5) et quatre valeurs arrondiraient toujours vers le haut (7,6 à 7,9). En d’autres termes, au fil du temps, 5,5 valeurs arrondiraient à la baisse et 4,5 valeurs arrondiraient à la hausse. Dans une vraie application uniforme de la probabilité statistique, 5 valeurs devraient arrondir vers le bas et 5 vers le haut, mais la « déformation » de la valeur centrale (7,5) fait un biais en faveur de l’arrondi vers le bas et contre l’arrondi vers le haut.

Alors, quelle théorie d’arrondi est correcte? Est-ce que 7,5 devrait arrondir à la moitié du temps et à la baisse la moitié du temps, ou devrait-il toujours arrondir à la hausse? Microsoft a évidemment pris une décision, car il arrondit toujours 7,5 vers le haut (la balle de tennis tombe toujours à droite pour les valeurs positives et à gauche pour les valeurs négatives). La décision de Microsoft signifie-t-elle que toujours arrondir 0,5 vers le haut est correct? Votre position dans la guerre de religion arrondie déterminera votre réponse.

Eh bien, peut-être qu’un autre point de données vous aidera. Il semble qu’il existe une norme ANSI sur toute cette question. Un abonné a indiqué qu’il avait toujours suivi les normes ASTM E29 et ANSI Z25.1, qui spécifient toutes deux qu’une valeur fractionnaire exacte de 0,5 doit être arrondie au nombre le plus proche se terminant par un chiffre pair. Si vous devez effectuer ce type d’arrondi, la formule appropriée à utiliser est la suivante:

=IF(A1-INT(A1)-0.5,EVEN(ROUNDDOWN(A1,0)),ROUND(A1,0))

Pour voir comment cela peut affecter le résultat de l’arrondi, j’ai généré une série de 25 000 nombres aléatoires entre 1 et 100, où chaque résultat avait jusqu’à deux décimales. J’ai ensuite arrondi les valeurs à une valeur entière en utilisant la fonction ROUND régulière dans une colonne, et dans la colonne suivante, j’ai arrondi les nombres en utilisant la formule ci-dessus. J’ai ensuite additionné chaque colonne pour voir quelle méthode d’arrondi produisait des résultats plus proches de la somme d’origine. Dans mon test, les résultats étaient plus de 50% plus proches de la somme d’origine en utilisant la formule ci-dessus plutôt que la fonction ROUND d’Excel seule.

J’ai ensuite généré 25 000 nombres aléatoires avec jusqu’à trois décimales, et les résultats étaient les mêmes – la formule était plus proche qu’un ROUND générique. Il en va de même pour les nombres à quatre et cinq décimales.

Une chose que j’ai remarquée lors de mes tests était que dans le premier ensemble de données de test (nombres aléatoires avec jusqu’à deux décimales), il y avait 234 valeurs qui correspondaient exactement aux critères d’être exactement 0,5 (et donc éligibles pour arrondir vers le haut ou vers le bas ). Dans la liste à trois décimales, le nombre de correspondances est passé à 14 valeurs, avec quatre décimales, il était de 2 valeurs et avec cinq décimales, il était de 0 valeurs. Il va de soi que moins il y a de valeurs qui répondent aux critères de se terminant par 0,5, moins il est nécessaire d’appliquer le « arrondi vers le haut ou vers le bas »

logique. Ainsi, la formule d’arrondi ci-dessus perd son efficacité lorsque vous commencez à traiter des nombres ayant quatre, cinq, six chiffres ou plus à droite de la virgule décimale en raison du fait qu’il existe des correspondances au centre exact.

Toute discussion sur l’arrondissement, bien sûr, doit supposer que vous arrondissez des valeurs brutes, et non des valeurs précédemment arrondies. Par exemple, si une valeur brute est 14,46 et que vous l’arrondissez à 14,5, il serait inapproprié d’arrondir plus tard le 14,5 à 15. La procédure correcte serait d’examiner le 14,46 original, qui devrait arrondir à 14. Ainsi, vous devrait toujours utiliser ROUND comme l’une de vos dernières étapes dans l’utilisation des nombres, et non comme l’une des premières. Cela signifie que lorsque vous utilisez des fonctions d’agrégation, telles que SOMME ou MOYENNE, vous ne devez pas les appliquer à des valeurs qui ont déjà été arrondies. Au lieu de cela, vous devez SUM ou MOYENNE les valeurs brutes, puis arrondissez la SUM ou la MOYENNE. Vous obtiendrez des résultats plus précis en vous rappelant cette astuce.

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (2829) s’applique à Microsoft Excel 2007, 2010, 2013 et 2016.