Fórmula genérica
(=MAX(IF(TEXT(dates,"ddd")="Mon",values)))
Resumo
Para encontrar o valor máximo em um determinado dia da semana (ou seja, segunda, terça, quarta, etc.), você pode usar uma fórmula de matriz simples com base nas funções MAX, IF e TEXT. No exemplo mostrado, a fórmula na célula F5 é:
=MAX(IF(TEXT(dates,"ddd")=F4,values))
Onde datas (B5: B15) e valores (C5: C15) são intervalos nomeados.
Observação: esta é uma fórmula de matriz e deve ser inserida com Control + Shift + Enter.
Explicação
Trabalhando de dentro para fora, a função TEXT é usada para extrair um valor do dia da semana para cada data:
=TEXT(dates,"ddd")
Isso resulta em uma matriz como esta:
("Mon";"Tue";"Wed";"Thu";"Fri";"Mon";"Tue";"Wed";"Thu";"Fri";"Mon")
que é então comparado ao texto em F4, "seg". O resultado é outra matriz, que contém apenas valores TRUE e FALSE:
(TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE)
Observe que cada TRUE corresponde a uma segunda-feira. Essa matriz é retornada dentro da função IF como o teste lógico. Ele atua como um filtro para filtrar os valores em outros dias da semana. O resultado final de IF, que é retornado diretamente para a função MAX, é assim:
=MAX((85;FALSE;FALSE;FALSE;FALSE;94;FALSE;FALSE;FALSE;FALSE;52))
MAX ignora automaticamente os valores FALSE e retorna o maior valor restante, 94.
Com AGGREGATE
Para uma fórmula um pouco mais geek que não requer control + shift + enter, você pode usar a função AGGREGATE desta forma:
=AGGREGATE(14,6,values/(TEXT(dates,"ddd")=F4),1)
Esta é a fórmula usada na célula F6 no exemplo mostrado. Aqui, fornecemos AGGREGATE 14 para o argumento da função (LARGE) e 6 para o argumento da opção (ignorar erros). Em seguida, construímos uma expressão lógica usando a função TEXT para verificar todas as datas das segundas-feiras. O resultado dessa operação é uma matriz de valores TRUE / FALSE, que se tornam o denominador dos valores originais. Quando usado em uma operação matemática, FALSE é avaliado como zero e gera um # DIV / 0! erro. TRUE é avaliado como 1 e retorna o valor original. A matriz final de valores e erros atua como um filtro. AGGREGATE ignora todos os erros e retorna o maior (máximo) dos valores remanescentes.
MAXIFS
A função MAXIFS, disponível no Excel Office 365, pode retornar um valor máximo usando um ou mais critérios sem a necessidade de uma fórmula de matriz. No entanto, MAXIFS é uma função baseada em faixa e não permite que outras funções, como TEXT, processem valores em faixas de critérios. No entanto, você pode adicionar uma coluna auxiliar aos dados, gerar valores de dias da semana com TEXT e usar MAXIFS com a coluna auxiliar como intervalo de critérios.
