Fórmula genérica
(=MAX(IF(TEXT(dates,"ddd")="Mon",values)))
Resumen
Para encontrar el valor máximo en un día de la semana determinado (es decir, lunes, martes, miércoles, etc.), puede usar una fórmula de matriz simple basada en las funciones MAX, IF y TEXT. En el ejemplo que se muestra, la fórmula en la celda F5 es:
=MAX(IF(TEXT(dates,"ddd")=F4,values))
Donde las fechas (B5: B15) y los valores (C5: C15) son rangos con nombre.
Nota: esta es una fórmula de matriz y debe ingresarse con Control + Shift + Enter.
Explicación
Trabajando de adentro hacia afuera, la función TEXTO se usa para extraer un valor de día de la semana para cada fecha:
=TEXT(dates,"ddd")
Esto da como resultado una matriz como esta:
("Mon";"Tue";"Wed";"Thu";"Fri";"Mon";"Tue";"Wed";"Thu";"Fri";"Mon")
que luego se compara con el texto en F4, "Mon". El resultado es otra matriz, que contiene solo valores VERDADERO y FALSO:
(TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE)
Tenga en cuenta que cada VERDADERO corresponde a un lunes. Esta matriz se devuelve dentro de la función IF como prueba lógica. Actúa como un filtro para filtrar valores en otros días de la semana. El resultado final de IF, que se devuelve directamente a la función MAX, se ve así:
=MAX((85;FALSE;FALSE;FALSE;FALSE;94;FALSE;FALSE;FALSE;FALSE;52))
MAX ignora automáticamente los valores FALSE y devuelve el valor restante más alto, 94.
Con AGREGAR
Para una fórmula un poco más geek que no requiere control + shift + enter, puede usar la función AGREGAR de esta manera:
=AGGREGATE(14,6,values/(TEXT(dates,"ddd")=F4),1)
Esta es la fórmula utilizada en la celda F6 en el ejemplo que se muestra. Aquí damos AGREGADO 14 para el argumento de la función (GRANDE) y 6 para el argumento de la opción (ignorar los errores). Luego, construimos una expresión lógica usando la función TEXTO para verificar todas las fechas de los lunes. El resultado de esta operación es una matriz de valores VERDADERO / FALSO, que se convierte en el denominador de los valores originales. Cuando se usa en una operación matemática, FALSE se evalúa como cero y arroja un # DIV / 0! error. VERDADERO se evalúa como 1 y devuelve el valor original. La matriz final de valores y errores actúa como un filtro. AGGREGATE ignora todos los errores y devuelve el mayor (máximo) de los valores supervivientes.
MAXIFS
La función MAXIFS, disponible en Excel Office 365, puede devolver un valor máximo utilizando uno o más criterios sin la necesidad de una fórmula de matriz. Sin embargo, MAXIFS es una función basada en rangos y no permitirá que otras funciones como TEXT procesen valores en rangos de criterios. Sin embargo, puede agregar una columna auxiliar a los datos, generar valores de día de la semana con TEXT y luego usar MAXIFS con la columna auxiliar como rango de criterios.
