Fórmula genérica
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)),ROW(data), data)))
Resumen
Para promediar los últimos 3 valores numéricos en un rango, puede usar una fórmula de matriz basada en una combinación de funciones para introducir los últimos n valores numéricos en la función PROMEDIO. En el ejemplo que se muestra, la fórmula en D6 es:
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)), ROW(data), data)))
donde "datos" es el rango con nombre B5: B13.
Nota: esta es una fórmula de matriz y debe ingresarse con control + shift + enter.
Explicación
La función PROMEDIO calculará un promedio de números presentados en una matriz, por lo que casi todo el trabajo en esta fórmula es generar una matriz de los últimos 3 valores numéricos en un rango. Trabajando desde adentro hacia afuera, la función SI se usa para "filtrar" valores numéricos:
IF(ISNUMBER(data),ROW(data))
La función ISNUMBER devuelve VERDADERO para valores numéricos y FALSO para otros valores (incluidos los espacios en blanco), y la función FILA devuelve números de fila, por lo que el resultado de esta operación es una matriz de números de fila que corresponden a entradas numéricas:
(5;6;FALSE;8;9;10;FALSE;12;13)
Esta matriz entra en la función LARGE con la constante de matriz (1,2,3) para k. LARGE ignora automáticamente los valores FALSE y devuelve una matriz con los 3 números más grandes, que corresponden a las últimas 3 filas con valores numéricos:
(13,12,10)
Esta matriz entra en la función BUSCAR como valor de búsqueda. La función FILA proporciona la matriz de búsqueda y la matriz de resultados es el rango denominado "datos":
LOOKUP((13,12,10), ROW(data), data))
BÚSQUEDA luego devuelve una matriz que contiene los valores correspondientes en "datos", que se alimenta en PROMEDIO:
=AVERAGE((100,92,90))
Manejo de menos valores
Si el número de valores numéricos cae por debajo de 3, esta fórmula devolverá el error #NUM ya que GRANDE no podrá devolver 3 valores según lo solicitado. Una forma de manejar esto es reemplazar la constante de matriz codificada de forma rígida (1,2,3) con una matriz dinámica creada usando INDIRECTO como este:
ROW(INDIRECT("1:"&MIN(3,COUNT(data))))
Aquí, MIN se utiliza para establecer el límite superior de la matriz en 3 o el recuento real de valores numéricos, el que sea menor.
Nota: Me encontré con este enfoque inteligente en chandoo.org, en una respuesta de Sajan a una pregunta similar.
