Calcular el promedio de los últimos n elementos de una serie.

miércoles, diciembre 18, 2013

Un lector me envía esta consulta:

En la columna A tengo una lista larga de nombres (por ej A, B, A, A , B, A; etc); en la columna B tengo un puntaje asociado a cada nombre.
Necesito calcular el promedio de los últimos 4 (o podría ser 5 o 6) puntajes correspondientes al nombre respectivo.
Supongamos que esta es nuestra lista

Lista no ordenada de valores

Como queremos calcular usando los últimos 4 (o cualquier otro número) miembros de la lista, por nombre, vamos a crear una columna auxiliar con el número de orden para cada valor de cada nombre. Esto lo haremos con la función CONTAR.SI

lista con numero de orden


En la fórmula =CONTAR.SI($A$3:A3,A3) la primera referencia del rango de búsqueda ($A$3) es absoluta. De esta manera la referencia se va "expandiendo" a medida que copiamos la fórmula a los largo de la columna

lista con numero de orden


Una vez agregada la columna auxiliar podemos calcular el promedio de los últimos n valores de cada nombre usando esta fórmula

=SUMAPRODUCTO((Nombre=G2)*(Nro._Orden>=MAX((Nombre=G2)*(Nro._Orden))-G3+1)*Valor)/SUMAPRODUCTO((Nombre=G2)*(Nro._Orden>=MAX((Nombre=G2)*(Nro._Orden))-G3+1))

desmostración de uso de fórmula


Personalmente no me gustan esta "super fórmulas", por lo que prefiero dividirlas en dos (o más) fórmulas intermedias

uso de SUMAPRODUCTO


La fórmula en la celda G5 (Suma) es

=SUMAPRODUCTO((Nombre=G2)*(Nro._Orden>=MAX((Nombre=G2)*(Nro._Orden))-G3+1)*Valor)
La fórmula en G6 (Recuento) es

=SUMAPRODUCTO((Nombre=G2)*(Nro._Orden>=MAX((Nombre=G2)*(Nro._Orden))-G3+1))
Y la obvia fórmula en G7 (Promedio) es =G5/G6

Antes de analizar cómo funcionan las fórmulas aclaremos que hemos asignado los rangos de valores a nombres definidos

Administrador de nombres



¿Cómo funciona la fórmula en G5?

Primero debemos calcular cuál en el número de orden mayor para el nombre en la lista (el último, por ejemplo para B será 13), lo que hacemos con

MAX((Nombre=G2)*(Nro._Orden))

Como esta fórmula está incluida dentro de SUMAPRODUCTO, Excel la calculará en forma matricial. Si queremos usarla independientemente tendremos que introducirla apretando simultáneamente Ctrl-Mayúscula-Enter.

Para que Excel busque los últimos 4 valores usamos

(Nro._Orden>=MAX((Nombre=G2)*(Nro._Orden))-G3+1)

que en el caso de buscar el resultado para B, da 10. Esto podemos comprobarlo seleccionando la parte de la fórmula y apretando F9 (luego apretamos ESC para deshacer el cálculo)

calcular parte de una formula






En el próximo paso

(Nro._Orden>=MAX((Nombre=G2)*(Nro._Orden))-G3+1)

SUMAPRODUCTO asigna el valor VERDADERO a todos los valores de número de orden que sean mayores o iguales a 10. Siendo 13 el mayor, habrá 4 VERDADEROS en el vector.

La expresión (Nombre=G2) dentro de SUMAPRODUCTO crea una vector con valores VERDADERO para las fila del nombre buscado y FALSO para el resto.

La multiplicación de ambos vectores crea un vector de valores VERDADERO sólo para las filas en que ambas condiciones se cumplan.

Finalmente al multiplicar este vector por el de los valores, obtenemos la suma de las filas que cumplen ambas condiciones simultáneamente.

La fórmula para el Recuento hace lo mismo sin multiplicar por los valores.

Un último detalle es el formato numérico personalizado en la celda G3

formato numerico personalizado


El archivo puede descargarse aquí

2 comments:

Ismael,  24 diciembre, 2013 00:12  

Hola Jorge me gusto e intereso mucho tu post, fijate que yo necesito algo mas o menos asi, pero para sacar valores totales, me gustaria mucho si me podrias ayudar en mi duda y/o ponerme en contacto contigo para asi explicarte a detalle y enviarte un pequeño extracto de mi archivo, ojala y me puedas echar la mano, agradeciendo tu atencion, quedo a tus ordenes, gracias.

Jorge Dunkelman 24 diciembre, 2013 07:23  

Para ponerte en contacto conmigo fijate en el enlace Ayuda (en la parte superior del blog).

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP