Vous l’avez vu aux Jeux olympiques et dans d’autres événements sportifs: le score moyen d’un athlète est déterminé en jetant le score le plus élevé et le score le plus bas, puis en faisant la moyenne du reste. Vous devrez peut-être effectuer des types de moyennes similaires. Par exemple, vous pouvez être un enseignant et avoir besoin d’exclure les deux scores d’affectation les plus bas avant de calculer une moyenne.

Pour effectuer ce type de calcul de moyenne, tout ce que vous devez vous rappeler est qu’une moyenne est calculée en additionnant toutes les valeurs d’une plage, puis en divisant cette somme par le nombre d’éléments de cette plage. La fonction SOMME fournit facilement la somme et la fonction COUNT peut être utilisée pour connaître le nombre d’éléments dans la plage. Comment exclure les deux valeurs les plus basses de la plage? Vous pouvez utiliser la fonction SMALL.

Considérez la formule suivante, qui suppose que vous voulez trouver une moyenne ajustée de la plage A10: A14:

=(SUM(A10:A14)-SMALL(A10:A14,1)-SMALL(A10:A14,2))/(COUNT(A10:A14)-2)

La fonction SMALL est utilisée pour déterminer les deux valeurs les plus basses de la plage, et celles-ci sont soustraites de la somme globale de la plage. La valeur résultante est ensuite divisée par le COUNT de valeurs de la plage.

Notez également que la valeur COUNT est diminuée de 2 pour compenser le fait que vous ignorez les deux valeurs les plus basses.

Une autre façon de calculer la même moyenne consiste à utiliser une formule matricielle.

Le suivant fait l’affaire:

=AVERAGE(IF(A10:A14>SMALL(A10:A14,2),A10:A14))

Puisqu’il s’agit d’une formule matricielle, vous devez l’entrer en appuyant sur Ctrl + Maj + Entrée au lieu d’appuyer simplement sur Entrée. Cette formule repose toujours sur l’utilisation de la fonction SMALL, mais elle utilise également la fonction MOYENNE réelle pour renvoyer un résultat. Puisqu’il s’agit d’une formule matricielle, elle examine chacune des valeurs du tableau (la plage) et ne les considère à utiliser dans la moyenne que si elles sont supérieures à la deuxième plus petite valeur du tableau.

Bien que la formule matricielle soit plus courte que la formule régulière plus longue, il y a une mise en garde à garder à l’esprit: la formule matricielle produira un résultat indésirable s’il y a une « égalité » bidirectionnelle dans la deuxième valeur la plus basse de la plage, ou une égalité à trois dans la valeur la plus basse. Par exemple, si les valeurs moyennées sont 3, 2, 10, 3 et 7, la formule matricielle produira une moyenne de 8,5. Pourquoi? Parce que seules les valeurs 10 et 7 sont au-dessus de la deuxième valeur la plus basse, et la moyenne de ces deux est de 8,5.

Si vous utilisez la formule la plus longue, présentée pour la première fois ci-dessus, la moyenne renvoyée est de 6,666667, soit la moyenne de 10, 3 et 7.

Si vous essayez ces formules et qu’elles ne fonctionnent pas, vous devez vérifier que vous avez installé l’Outil d’analyse. La fonction SMALL fait partie de ce ToolPak.

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (2050) s’applique à Microsoft Excel 97, 2000, 2002 et 2003. Vous pouvez trouver une version de cette astuce pour l’interface ruban d’Excel (Excel 2007 et versions ultérieures) ici:

link: / excelribbon-Excluding_Values_from_Averaging [Excluding Values ​​from Averaging].