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

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

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

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

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))

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

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

¿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)

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

El archivo puede descargarse aquí