Ken a écrit sur un problème qu’il rencontrait avec la fonction GEOMEAN. Lorsqu’il tente d’utiliser la fonction sur un grand nombre de valeurs (3 500 lignes de données), il obtient une valeur d’erreur #NUM renvoyée.

La fonction GEOMEAN est utilisée pour renvoyer la moyenne géométrique d’une série de valeurs. Le GEOMEAN de n nombres est la n-ième racine du produit des nombres. Par exemple, s’il y a quatre valeurs dans une série (A à D), le produit de ces nombres est A B C * D et le GEOMEAN est la quatrième racine de ce produit.

L’erreur #NUM serait renvoyée si l’une des trois conditions était remplie: l’une des valeurs était égale à zéro, l’une des valeurs était négative ou les limites d’Excel étaient dépassées. Il est probable que ce soit cette dernière condition que Ken rencontre, en particulier si l’une de ses 3 500 valeurs est grande.

Puisque GEOMEAN trouve le produit des 3500 nombres (les multiplie tous les uns par les autres), puis prend la racine nième, le produit peut facilement être trop grand pour Excel. Le plus grand nombre positif dans Excel est 9.99999999999999 * 10 ^ 307 (en notation scientifique, il s’écrit 9.99999999999999E + 307). Si le produit dépasse ce nombre, vous obtiendrez une erreur #NUM pour la fonction.

La solution consiste à utiliser des journaux pour faire le calcul. Ceci est plus facile à comprendre lorsque vous regardez une transformation de la fonction GEOMEAN:

GEOMEAN = (X1X2X3...Xn)^ (1/n)

ln(GEOMEAN) = ln((X1X2X3...Xn)^ (1/n))

ln(GEOMEAN) = (1/n)  ln(X1X2X3...*Xn)

ln(GEOMEAN) = (1/n) * (ln(X1)+ln(X2)+ln(X3)+...+ln(Xn))

ln(GEOMEAN) = average(ln(X1)+ln(X2)+ln(X3)+...+ln(Xn))

GEOMEAN = exp(average(ln(X1)+ln(X2)+ln(X3)+...+ln(Xn)))

Si vous suivez ce qui précède, vous voyez que GEOMEAN est équivalent à l’exposant de la moyenne des journaux des valeurs. Vous pouvez calculer le résultat souhaité en utilisant la formule matricielle suivante au lieu de la fonction GEOMEAN:

=EXP(AVERAGE(LN(A1:A3500)))

Cela suppose que les valeurs souhaitées se situent dans la plage A1: A3500. Puisqu’il s’agit d’une formule matricielle, vous devez l’entrer dans une cellule en utilisant Ctrl + Maj + Entrée.

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (2580) 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-Using_GEOMEAN_with_a_Large_List [Utilisation de GEOMEAN avec une grande liste].