EXCEL: Promedio de los últimos 3 valores numéricos

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 alimentar los últimos n valores numéricos en la función PROMEDIO. En el ejemplo que se muestra, la fórmula en D6 es:

{=PROMEDIO(BUSCAR(K.ESIMO.MAYOR(SI(ESNUMERO(data),FILA(data)),{1,2,3}), FILA(data), data))}

donde "datos" es el rango nombrado B5: B13.

Nota: esta es una fórmula de matriz, y debe ingresarse con control + shift + enter.

Cómo funciona esta fórmula

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 de adentro hacia afuera, la función SI se usa para "filtrar" valores numéricos:

SI(ESNUMERO(data),FILA(data))

La función ESNUMERO 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 un número de fila de matriz que corresponde a entradas numéricas:

{5;6;FALSO;8;9;10;FALSO;12;13}

Esta matriz entra en la función K.ESIMO.MAYOR con la constante de matriz {1,2,3} para k. K.ESIMO.MAYOR ignora automáticamente los valores FALSO 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}

Este conjunto entra en la función BUSCAR como valor de búsqueda. La matriz de búsqueda es proporcionada por la función FILA, y la matriz de resultados es el rango "datos" nombrado:

BUSCAR({13,12,10}, FILA(data), data))

BUSCAR luego devuelve una matriz que contiene los valores correspondientes en "datos", que se alimenta a PROMEDIO:

=PROMEDIO({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 K.ESIMO.MAYOR no podrá devolver 3 valores según lo solicitado. Una forma de manejar esto es reemplazar la constante de matriz codificada {1,2,3} con una matriz dinámica creada usando INDIRECTO de esta manera:

FILA(INDIRECTO("1:"&MIN(3,CONTAR(data))))

Aquí, MIN se usa 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.