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

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: MS Excel

Gráficos dinámicos – Mostrar puntos en función de valores.

Ya hemos visto una técnica para determinar cuantos puntos de una serie mostrar en un gráfico.
En esta entrada veremos como determinar la cantidad de puntos a mostrar en función de un determinado valor.

Supongamos que queremos generar un gráfico de columnas a partir de esta tabla de ventas (el archivo con el ejemplo se puede descargar aquí)



Nuestro objetivo es determinar la cantidad de puntos a exhibir en función de un determinado valor. Digamos que queremos exhibir los 5 meses con más ventas.

Empezamos por crear una columna auxiliar, con la fórmula

=JERARQUIA(B2,$B$2:$B$13)+CONTAR.SI($B$2:B2,B2)-1



Esta fórmula la otorga a cada valor un número de orden. Usamos CONTAR.SI para "desempatar" en caso que dos meses tenga la misma suma de ventas.

Ahora creamos una tabla auxiliar donde ordenamos la tabla original de mayor a menor. Para lograr esto usamos las funciones INDICE y COINCIDIR



En el rango E2:E13 ponemos la serie del 1 al 12 que nos servirá como referencia para ordenar los valores. En el rango F2:F13 ponemos la fórmula

=INDICE($A$2:$A$13,COINCIDIR(E2,$C$2:$C$13,0))

Esta fórmula usa los valores del rango E2:E13 para obtener el mes adecuado. Lo mismo hacemos en el rango G2:G13 para poner la suma del mes.

Nuestro próximo paso es crear el gráfico. En esta etapa veremos todos los meses



Ahora tenemos que crear dos rangos dinámicos usando nombres (ver la nota del enlace más arriba).
Creamos dos nombres

mes =DESREF(Hoja1!$F$2,0,0,Hoja1!$I$1,1)
suma = DESREF(Hoja1!$G$2,0,0,Hoja1!$I$1,1)

Como ven, hemos ligado los nombres a la celda I1 de la hoja. En esta celda ponemos, en esta etapa, la cantidad de puntos de la serie que queremos mostrar. Como estos nombres se refieren a la tabla auxiliar, donde hemos ordenado los datos en forma decreciente, si ponemos 5 en la celda I1, los rangos dinámicos mostrarán los primeros cinco meses de la tabla, que son los primeros 5 meses con mayores ventas.

Nuestro próximo paso es reemplazar en la función SERIES del gráfico, los rangos de los valores por los nombres que acabamos de crear.

Seleccionamos el gráfico y abrimos el menú Datos de Origen



Y reemplazamos los rangos del gráfico por los nombres



Esto también se puede hacer seleccionando la serie de datos en el gráfico y reemplazando los valores en la función SERIES que aparece en la barra de fórmulas.

Cada vez que reemplacemos el valor en la celda I1, el gráfico mostrará los valores correspondientes



Nuestro último paso es que el gráfico dependa de un valor de ventas. Por ejemplo, ponemos 60000 en una celda y que el gráfico nos muestre todos los meses con ventas mayores a 60000. Para esto agregamos una celda auxiliar, I2, donde ponemos el monto de ventas a partir del cual queremos mostrar los meses. En la celda I1 ponemos esta fórmula

=CONTAR.SI(G2:G13,">"&I2)

Ahora nuestro gráfico muestra todos los meses con ventas mayores al monto introducido en la celda I2

Technorati Tags: MS Excel

Sumas condicionales con SUMAPRODUCTO

La función SUMAR.SI nos permite realizar sumas de acuerdo a una condición.
Ya hemos mostrado en este blog cómo hacer sumas condicionales con más de una condición
.
En esta nota veremos como enfrentarnos con situaciones especiales del tipo, por ejemplo, cómo sumar los cinco mayores números de una lista.

Por ejemplo, supongamos esta lista de números

Los cinco números mayores de la lista ( 94, 98, 93, 88, 87) suman 454. No hay forma en la cual podamos usar SUMAR.SI para sumar los números mayores de la lista. Pero si podemos hacerlo con SUMAPRODUCTO (o con fórmulas matriciales). EN nuestro caso

=SUMAPRODUCTO(lista*(JERARQUIA(lista;lista)<=5)) donde lista es un nombre que define el rango A2:A21 SUMAPRODUCTO crea dos matrices. Una contiene todos los números del rango "lista" (A2:A21); la otra matriz contiene valores VERDADERO y FALSO producidos por la función JERARQUIA. Cuando el valor calculado es menor o igual a 5, es VERDADERO; en caso contrario, FALSO. Como ya hemos visto, Excel interpreta VERDADERO como 1 al emplearlo en una operación y FALSO como 0.

Para calcular los 5 menores podemos recurrir a la función CONTAR, para determinar cuantos miembros hay en el rango y así poder determinar cuales son los últimos 5 en orden decreciente

=SUMAPRODUCTO(lista*(JERARQUIA(lista;lista)>CONTAR(lista)-5))

o esta mas sencilla, sin CONTAR

=SUMAPRODUCTO(lista*(JERARQUIA(lista;lista;1)<=5))

Si queremos sumar todos los números pares usamos la fórmula

=SUMAPRODUCTO(lista*(RESIDUO(lista;2)=0))

o

=SUMAPRODUCTO(lista*(RESIDUO(lista;2)<>1))

Para los impares usamos

=SUMAPRODUCTO(lista*RESIDUO(lista;2))

que es el equivalente a

=SUMAPRODUCTO(lista*(RESIDUO(lista;2)=1))

pero nos ahorra dos paréntesis.

La función RESIDUO(número;2) da 0 si el número es par. Por eso la expresión RESIDUO(lista;2)=0 es VERDADERO si el número en la "lista" es par.

Para sumar los múltiplos de un determinado número, también podemos usarla función RESIDUO. Por ejemplo, para sumar todos los números que son múltiplos de 3 en nuestra lista, usamos la fórmula

=SUMAPRODUCTO(lista*(RESIDUO(lista;3)=0))

Otro uso de RESIDUO es sumar todos los valores que de las filas pares o impares. Por ejemplo, para sumar todos los valores del rango "lista" que están en filas pares, usamos

=SUMAPRODUCTO(lista*(RESIDUO(FILA(lista);2)=0))

De la misma manera, para sumar los valores en filas impares podemos usar:

=SUMAPRODUCTO(lista*(RESIDUO(FILA(lista);2)<>0))

Si queremos sumar cada tres filas, a partir de la fila 3 podemos usar esta fórmula

=SUMAPRODUCTO(lista*(RESIDUO(FILA(lista);3)=0))

Si queremos sumar cada 3 filas, pero empezando de la primer fila del rango, usamos esta fórmula

=SUMAPRODUCTO((RESIDUO(FILA(lista);3)=2)*lista)

donde "2" es el número de la primer fila del rango.

La fórmula general es =SUMAPRODUCTO((RESIDUO(FILA(lista);n)=m)*lista)

Donde n es el "escalón" (número de filas que queremos saltear en la cuenta) y m es la fila de donde comenzamos a sumar.

El archivo con las fórmulas se puede descargar aquí.

Technorati Tags: MS Excel