Máximo, Mínimo y K.esimo condicionales en Excel

Excel no cuenta con funciones para calcular máximos o mínimos sujetos a condiciones. Por ejemplo, en esta lista de valores por departamentos

Para encontrar el valor máximo y el mínimo del Depto. 4 tenemos que usar funciones matriciales.
En la celda F3 ponemos la fórmula

=MAX((B3:B18=F2)*C3:C18) 

en forma matricial (se introduce apretando simultáneamente Ctrl-Mayúsculas-Enter), y en la celda F4 la fórmula matricial

=MIN(SI(B3:B18=F2,C3:C18))

La nueva función AGREGAR  (disponible a partir de la versión 2010 de Excel) nos permite encontrar máximos y mínimos, como también valores "k.ésimos" (segundo mayor, tercer menor, etc.) sin necesidad de usar fórmulas matriciales.



Para encontrar el valor máximo del Depto. 4 usamos esta fórmula
=AGREGAR(14,6,C3:C18/(B3:B18=$F$2),1)
y para el mínimo
=AGREGAR(15,6,C3:C18/(B3:B18=$F$2),1)
Veamos cómo funciona esta fórmula:

Como explicamos en la nota mecionada, AGREGAR cuenta con 19 operaciones siendo 14 K.ESIMO.MAYOR y la operación 15, K.ESIMO.MENOR.
El segundo argumento (Opciones = 6) indica que los errores serán ignorados.
La expresión C3:C18/(B3:B18=$F$2) en la fórmula crea una matriz que contiene errores DIV/0! como podemos ver en esta columna auxiliar

cuando la fila evaluada no corresponde al Depto. 4,que serán ignorados, dado que hemos establecido el argumento Opciones con el valor 6.
El argumento "k" funciona de la misma manera que en la función K.ESIMO.MAYOR. Al establecer 1, el resultado será el mayor de los valores.