Resolver el error #NUM en el cálculo de la media geométrica en Excel

sábado, marzo 08, 2008

Esta nota será un tanto exótica para la mayoría de mis lectores, pero supongo que será útil para otros, como lo ha sido para una lectora de México que me consulta sobre la función MEDIA.GEOM.
Esta función, citando la ayuda de Excel devuelve la media geométrica de una matriz o de un rango de datos positivos. Por ejemplo, es posible utilizar la función MEDIA.GEOM para calcular la tasa de crecimiento promedio, dado un interés compuesto por tasas variables.

El uso de la palabra "devuelve" para indicar cuál es el resultado previsto de una función, sigue molestándome a pesar de los años que llevo leyéndola en la ayuda de Excel y en distintas notas y comentarios. No le hemos dado nada a la función, por lo que no veo que es lo que tendrá para devolvernos. Pero dejemos las cuestiones del lenguaje para otra oportunidad.

Mi lectora quería saber por qué la función MEDIA.GEOM que usaba en su cálculo daba como resultado el error #NUM, aún después de haber revisado que todos los valores en el rango fuesen numéricos.
Para entender por qué se genera el error empecemos por definir media geométrica:

la raíz n-ésima del producto de n números.

Cuando nuestro rango de número incluye números de gran magnitud puede generarse una situación de "overflow".

En elarchivo del ejemplo hay una lista con 50 valores.
En la columna B calculamos en cada línea el producto de todos los valores de la columna A hasta esa línea incluida. Podemos ver que al llegar a la línea 50, el resultado es #NUM



La forma de resolver el problema, es usar logaritmos, aquellos viejos (y odiados) conocidos de la época del secundario.
Empezamos por calcular los logaritmos de cada valor con la función LN




Luego calculamos el promedio de los logaritmos



Y finalmente calculamos la inversa con la función EXP, es decir, elevamos el número e al resultado del promedio



Podemos resumir todo este proceso en una sola fórmula matricial



La fórmula matricial =EXP(PROMEDIO(LN(A2:A51))) la introducimos pulsando simultáneamente Ctrl+Mayúsculas+Enter.

La idea de solucionar le problema con el uso de logaritmos fue tomada de la página Geometric Mean Calculations publicada por el Dr. Joe Costa


Technorati Tags:

6 comments:

Anónimo,  19 abril, 2009 18:32  

ola tengo una consulta
resulta q primero tengo q calcular un valor para despues sacar la media geometrica
por ejemplo en mi caso tengo q calcular 2 elevado a 2033 y ya calculando me da ERROR NUM entonces ni sikiera alcanzo a calcular el LN pk no se lo puedo sacar a la celda con el error.
hay alguna otra forma?

Jorge L. Dunkelman 19 abril, 2009 19:01  

Hola
por favor, fijate en la nota sobre del enlace en Contacto sobre cómo contactarse conmigo, en especial al uso del castellano.

Adrenaluna 13 enero, 2011 00:47  

Hola, navegando por internet en busca de una respuesta a mi inquietud, esta es la que mas se aproxima. Sin embargo las dudas se mantienen, mi caso es que presentamos licitaciones donde generalmente son de 120 proponentes hacia arriba. Muchas veces la propuesta que gana es el valor mas cercano a la media geometrica o a la media rmonica. Cuando intentamos verificar los datos con esos 130 numeros (por ejemplo) no nos da y no sabemos por que. Me dicen que en excel hay que sumar de maximo 30 casillas... pero si sumode 20 por ejemplo me da diferente. En fin.. no hemos logrado saber como sacan losdatos por excel. Tu me podrias ayudar?= Mi correo es julieth327@gmail.com

Jorge L. Dunkelman 13 enero, 2011 08:03  

Adrenaluna,
tendría que ver el archivo. Puedes mandarmelo a la dirección que figura en el enlace Ayuda.

Miguel Pinzón 07 julio, 2016 20:04  

tengo el mismo problema que Andrealuna, si es posible solucionarlo?

Jorge Dunkelman 07 julio, 2016 21:20  

Miguel, tal como le dije a Adrenaluna, tendría que ver el archivo, las fórmulas que estás aplicando, cómo están organizados los datos y cuál es el resultado esperado.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP