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í

Contar valores únicos en un rango con dos criterios.

Hace ya cinco años atrás publique una nota sobre cómo contar valores únicos en un rango. Siguiendo con el tema, un lector me consulta cómo contar los valores únicos en el rango pero con más de un criterio.
Por ejemplo, en nuestro ejemplo, contar vendedores por región. Digamos que tenemos una tabla de vendedores por región, pero por algún motivo hay líneas duplicadas

Podemos ver que el agente 3 y el agente 5 aparecen dos veces en la zona Norte. Tenemos seis líneas para la zona Norte pero sólo cuatro agentes.

Mi propuesta para este tipo de situaciones es usar tablas dinámicas (mi herramienta preferida, como ya habrán notado mis lectores habituales).

Empezamos por agregar una columna auxiliar a la tabla de datos para identificar el número de aparición del vendedor (suponemos que un vendedor no puede aparecer en dos zonas distintas)

En la columna “aux” usamos la fórmula =CONTAR.SI($B$2:B2,B2).

Luego creamos una tabla dinámica poniendo los campos Región y Agente en el área de filas, el campo “aux” como filtro del informe y el campo “Agente” también en el área de valores. Como éste no es un valor numérico, Excel usa CUENTA para totalizar los valores

Como puede verse, filtramos el informe poniendo el valor “1” en el campo “aux”.
Otra variación es usar la tabla dinámica como “motor de cálculo” y usar la función IMPORTARDATOSDINAMICOS para extraer el valor requerido.

Sencillamente ponemos en la celda C3 “=” y apuntamos a la celda correspondiente en la tabla dinámica; Excel crea la fórmula

=IMPORTARDATOSDINAMICOS("Agente",Hoja4!$A$3,"Region","Este")

Ahora remplazamos “Este” en la función por una referencia a la celda C2

Cada vez que remplazamos el valor de C2 por otra región, la fórmula se actualiza. El cuaderno que aparece abajo es interactivo.

Cuando CONTAR.SI da resultados erróneos

Si necesitamos contar condicionalmente, nuestra primera elección es la función CONTAR.SI. Pero en ciertas situaciones, CONTAR.SI puede dar resultados equivocados.

Veamos este ejemplo “basado en un hecho real” (uno de mis clientes). Supongamos una empresa que usa códigos alfanuméricos para identificar sus productos. Con este sistema, 12345 y 012345 son dos productos distintos. Ahora queremos contar cuántas veces se repite un código en una lista, por ejemplo en esta

Fácil, ¿no?
A simple viste vemos que 12345 se repite tres veces y 012345 se repite dos veces. Veamos que pasa con CONTAR.SI

Usamos =CONTAR.SI($A$2:$A$6,C2) en D2 y =CONTAR.SI($A$2:$A$6,C3) en D3 y en ambos casos el resultado es 5!

Esto se debe a que CONTAR.SI evalúa todo valor como número, de manera que 012345 es evaluado como número y por lo tanto es considerado idéntico a 12345.

La solución es usar SUMAPRODUCTO, de esta manera

=SUMAPRODUCTO(--($A$2:$A$6=C2))

SUMAPRODUCTO genera un vector de con los valores de la celda del rango

y los compara con el valor de la condición, generando un vector de valores VERDDERO t FALSO

Finalmente usamos el doble “--“ para convertir los VERDADERO en 1 y los FALSO en 0.

Quien no se sienta cómodo con el uso del doble “-“ puede multiplicar el vector por 1

=SUMAPRODUCTO(($A$2:$A$6=C2)*1)