Ken escribió sobre un problema que estaba teniendo con la función GEOMEAN. Cuando intenta usar la función en una gran cantidad de valores (3500 filas de datos), obtiene un valor de error #NUM devuelto.

La función GEOMEAN se utiliza para devolver la media geométrica de una serie de valores. El GEOMEAN de n números es la raíz n-ésima del producto de los números. Por ejemplo, si hay cuatro valores en una serie (de la A a la D), entonces el producto de esos números es A B C * D, y GEOMEAN es la cuarta raíz de ese producto.

El error #NUM se devolvería si se cumpliera alguna de las tres condiciones: cualquiera de los valores fuera igual a cero, cualquiera de los valores fuera negativo o se excedieran los límites de Excel. Es probable que sea esta última condición en la que se encuentre Ken, especialmente si alguno de sus 3500 valores es grande.

Dado que GEOMEAN encuentra el producto de los 3500 números (los multiplica todos entre sí) y luego obtiene la raíz n, el producto puede ser demasiado grande para Excel. El número positivo más grande en Excel es 9.99999999999999 * 10 ^ 307 (en notación científica, esto se escribe como 9.99999999999999E + 307). Si el producto supera este número, obtendrá un error #NUM para la función.

La solución es usar registros para hacer el cálculo. Esto es más fácil de entender cuando observa una transformación de la función 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 sigue lo anterior, verá que GEOMEAN es equivalente al exponente del promedio de los registros de los valores. Puede calcular el resultado deseado utilizando la siguiente fórmula de matriz en lugar de la función GEOMEAN:

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

Esto supone que los valores deseados están en el rango A1: A3500. Dado que es una fórmula de matriz, debe ingresarla en una celda usando Ctrl + Shift + Enter.

ExcelTips es su fuente de formación rentable en Microsoft Excel.

Este consejo (2580) se aplica a Microsoft Excel 97, 2000, 2002 y 2003. Puede encontrar una versión de este consejo para la interfaz de cinta de Excel (Excel 2007 y posterior) aquí:

link: / excelribbon-Using_GEOMEAN_with_a_Large_List [Usando GEOMEAN con una lista grande].