Fórmula de Excel: Promedio de los últimos 3 valores numéricos -

Tabla de contenido

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.

Articulos interesantes...