Total Anual Móvil (TAM) con Excel

A colación del post sobre totales acumulados me consulta un lector sobre la posibilidad de calcular el total anual móvil (TAM) con Power Query (y por mi cuenta voy a agregar como hacerlo con PowerPivot). Para ilustrar el tema ne envía un enlace a esta página del sitio SageAdvice de donde tomé esta tabla de datos

El Total Móvil Anual (TAM) es la suma de los doce últimos meses de lo que estamos midiendo, en nuestro caso las ventas. Así el TAM en enero incluye los meses Febrero a Diciembre del año anterior (2011 en la imagen arriba) y el mes de Enero del 2012. De la misma manera, Febrero suma los meses de Marzo a Diciembre del 2011 y Enero y Febrero del 2012, y así sucesivamente.

Ahora que sabemos qué es el TAM, vamos a ver como calcularlo con tres herramientas: Excel Clásico, Power Query y PowerPivot.

Excel 2016

La encuesta "Qué versión de Excel usas" que llevé adelante las últimas semanas me deparó ciertas sorpresas. Los resultados fueron

En total participaron 234 usuarios. Lo sorprendente no es que el 39% de los encuestados usen Excel 2013 (mi intuición decía que la mayoría usaba Excel 2010) sino que Excel 2016 con el 31% ocupara el segundo lugar.

La consecuencia más inmediata de la encuesta es que he instalado Office 2016, que será desde ahora la "versión oficial" de este blog.

Sobre las novedades y mejoras pueden leer en el blog oficial de Microsoft y en muchos otros buenos sitios y blogs de colegas. Así que aquí haré sólo una reseña, como para salvar un poco el prestigio de este blog que viene a ocuparse del tema con notable atraso (Office 2016 fue anunciado el 22 de setiembre del 2015).

Obtener y transformar (alias Power Query)

Lo que hasta la versión anterior era un complemento ha pasado a ser parte integral de Excel y ha sido rebautizado como "Obtener y transformar" en la ficha Datos de la cinta

Power Query, o con su nuevo nombre Obtener y Transformar", se encuentra en estado de permanente desarrollo y se ha vuelto, para los que analizamos gran cantidad de datos de distintas fuentes, en una herramienta fundamental.

Nuevas Funciones

En la última actualización Microsoft ha agregado nuevas funciones, pero me apresuro a aclarar que sólo están disponibles en la versión Office 365 (por suscripción).  Supongo y espero que en breve también estén disponibles para la versión de escritorio.

Es curioso que Microsoft no haya actualizado la versión de escritorio ya que estas funciones están disponibles en la versión on-line que es gratuita.

UNIRCADENAS (en inglés: TEXTJOIN)

Al igual que CONCATENAR esta función nos permite unir textos alojados en distintas celdas, pero ingresando el rango de celdas como un único argumento en lugar de celda por celda. Además, podemos definir el separador con un único argumento y definir si ignorar celdas vacías.

Por ejemplo

CONCAT

Reemplaza a CONCATENAR y es muy similar a UNIRCADENAS, pero con menos funcionalidades (sólo permite usar rangos e ignora las celdas vacías por definición)

SI.CONJUNTO (en inglés: IFS)

En lugar de “anidar” funciones SI para obtener el resultado que corresponde al cumplimiento de una determinada condición, podemos escribir las condiciones y el resultado en una serie y el resultado será el de la primera condición que se cumpla.

El caso clásico para esta función es determinar un porcentaje de descuento en función del monto de la compra

CAMBIAR (en inglés: SWITCH)

Función familiar para los usuarios de Access. Permite establecer el resultado como comparación de un variable con una serie de variables posibles

A diferencia de SI.CONJUNTO sólo permite comparar igualdades, es decir, si la variable coincide plenamente o no con los valores de la lista de comparación.

MAX.SI.CONJUNTO (en inglés: MAXIFS) y MIN.SI.CONJUNTO (en inglés: MINFIS)

Mis memoriosos seguidores recordarán el post sobre máximos y mínimos condicionales. Con estas dos nuevas funciones podemos dejar de lado todos los artilugios y calcular los máximos o mínimos sujetos a condiciones en forma natural.

Las funciones FILA() y COLUMNA()

Las funciones FILA() y COLUMNA() de Excel son de ese tipo de funciones que a primera vista parecen superfluas. FILA() da como resultado el número de fila de una referencia: COLUMNA() hace lo mismo en relación al número de columna de la referencia

Es decir, si ponemos en un celda =FILA(A4) el resultado será 4; si ponemos =FILA(), sin referencia, el resultado sera el número de fila de la celda que ocupa la fórmula.

El verdadero valor de esta funciones aparece cuando queremos crear series (vectores) de númenos enteros sucesivos lo que hacemos con una constante matricial.

En la nota sobre cálculo de vencimientos en día hábil usamos un constante matricial en esta fórmula

=MAX((C5+C4-{7;6;5;4;3;2;1;0})*(ESERROR(COINCIDIR(C5+C4-{7;6;5;4;3;2;1;0},Feriados,0)))*(DIASEM(C5+C4-{7;6;5;4;3;2;1;0},2)<6 span="">

(nótese el uso de "{" y "}" para crear la constante matricial y el ";" para indicar el sentido del vector). Esta notación tiene el problema de las definiciones regionales; en cierta definiciones se usa el "\" en lugar del punto y coma.
La función FILA supera este problema, ya que es independiente de las definiciones regionales:

=MAX((C5+C4+1-FILA(1:7))*(ESERROR(COINCIDIR(C5+C4+1-FILA(1:7);Feriados;0)))*(DIASEM(C5+C4+1-FILA(1:7);2)<6 span="">

Pero también esta notación tiene sus problemas ya que si copiamos la fórmula, la referencia cambia. Si ponemos en la celda A2 la fórmula =FILA(1:7), al copiarla a A3 veremos =FILA(2:8).

La solución es usar la función INDIRECTO de esta manera =FILA(INDIRECTO("1:7"))

Nótese que usamos comillas en la expresión "1:7", ya que INDIRECTO funciona con texto, no con valores numéricos.

La función FILAS()

He pasado una semana agitada. Todo empezó con un intimidante correo electrónico del departamento de informática que rezaba: "¡Urgente! Todos los laptops deben pasar una revisación. Traerlos de inmediato al laboratorio de informática". Todo en letras tamaño catástrofe. Sucede que algunos inocentes usuarios en la empresa habían caido en las garras del virus Ransom, ese que encripta los archivos del disco duro y exige rescate para quitar la codificación.
Tendría que haber fotografiado la cara del técnico cuando vio que tenía instalado Windows 10. Pero, ¿quién te autorizó a instalarlo?. Más que una pregunta era una acusación. Poniendo mi mejor cara de inocente le pregunté cuál era el problema. Me farfulló algo que no terminé de entender pero la sentencia fue terminante: te vamos a formatear el disco e instalar de nuevo el Windows 7, como si el disco duro fuera parte de mi organismo, carne de mi carne. Inútiles que fueron mis ruegos y súplicas pasé el resto de la semana reinstalando los programas que fueran despiadamente eliminados de mi laptop.Y toda este historia es para explicar por qué este post será mínimo pero espero que útil.

¿Recuerdan la nota sobre las funciones raramente usadas de Excel? A la lista de la nota, y de otras notas posteriores sobre las funciones CELDA, CARACTER y CODIGO, podemos agregar la función FILAS().

FILAS() da como resultado el número de filas de una referencia a un rango de filas o matriz

La utilidad de esta función es mucho mayor de lo que se desprende de la escueta información y la he utilizado en varias técnicas que aparecen en este blog.

Supongamos este ejemplo, basado en un anállisis que tuve que realiza para el departamento de mercadeo de mi organización. Tenemos una lista de órdenes de clientes en un rango que hemos definido como Tabla

Ahora supongamos que queremos calcular el número de filas en la tabla. Las técnica "tradicional" sería crear un nombre definido que se refiera a una fórmula con la función CONTARA. Pero si una de las celdas del rango esta vacía, el resultado será erróneo. La función FILAS() usada junto con la tabla soluciona este problema

La celda N3 contiene la fórmula =CONTARA(Tabla_Northwind[[#Todo],[Cliente]]) pero como la celda B5 está vacía el resultado es 19.
La celda N4 contiene la fórmula =FILAS(Tabla_Northwind[#Todo]) y dá el resultado correcto.

Si queremos calcular el porcentaje de órdenes de cada país del total de órdenes en la tabla podemos usar la fórmula =FILAS(Tabla_Northwind[#Datos])  como en la celda N2 en la imagen de abajo (y de paso vemos las ventajas del lenguaje estructurado de las tablas)

Las celdas N5 a N9 contienen la fórmula  =CONTAR.SI(Tabla_Northwind[Pais],M5)/FILAS(Tabla_Northwind[#Datos])

Al usar Tabla_Northwind[#Datos] la fila de los encabezados no es tomada en cuenta. Y por supuesto, ya que estamos usando tablas, a medida que agreguemos o eliminemos filas de la tabla, las fórmulas se adaptarán automáticamente.

Una curiosidad, no tan curiosa, es que FILAS() no crea una referencia circular también si la celda que contiene la fórmula es parte del rango de la referencia

Todo lo dicho aquí se aplica, por supuesto, a la función hermana COLUMNAS().

FILAS() y COLUMNAS() tienen dos primas cercanas, FILA() y COLUMNA() de las que hablaré en un futuro post.

Buen fin de semana.

Las funciones CARACTER y CODIGO de Excel

Allí por marzo de esta año publiqué una nota sobre "funciones raramente usadas en Excel". Integraban la lista, entre otras TIPO, SIGNO y RESIDUO. En agosto publique dos notas adicionales sobre las funciones CELDA y DELTA.
En este post hablaré sobre otras dos funciones que en mi opinión, raramente sufren de fatiga por uso: CARACTER y su inversa, la función CODIGO.

La descripción de la función CARACTER en excel reza: "devuelve el carácter especificado por el número de código del juego de caracteres establecido en su PC".

Así por ejemplo, el caracter perteneciente al número de código 36, en mi PC, es el símbolo $

Se impone la pregunta, ¿para qué usar la función si podemos introducir el símbolo usando el teclado? Hay situaciones en las cuales CARACTER es la única posibilidad, en especial cuando concatenamos textos en una celda.
Por ejemplo, tenemos el nombre de un producto y queremos que a continuación, en la misma celda, aparezca el símbolo de Copyright. Este símbolo, cuyo número de código es169, no está asignado a ninguna tecla. Pero podemos agregarlo de esta manera

También el símbolo TM (Trade mark, marca registrada, número de código 153) puede ser agregado de esta manera

Otro uso posible es cuando consolidamos texto de varias celdas en una única pero queremos presentarlo en líneas separadas dentro de la celda. En este caso podemos usar CARACTER(10), como se ve en este ejemplo (prestar atención que hemos aplicado "Ajustar Texto")

Otros dos códigos útiles son el 32 (espacio) y el 34 (comillas). Por ejemplo, si queremos introducir en una celda el texto Julio "Fantasma" Juárez

La función CODIGO es la inversa de CARACTER. Usando el caracter como argumento podemos averiguar el número de código. Así, la fórmula =CODIGO(") da como resultado 34.

La función TEXTO de Excel

Para crear y modelar nuestros informes Excel cuenta con una gran cantidad de herramientas. Muchas ocupan un lugar de privilegio, como las tablas dinámicas y los gráficos, y otras pasan desapercibidas, tal vez, por gran parte de los usuarios.

Una de estas es la función TEXTO(). Esta función nos permite mostrar un valor como texto con un formato, numérico, de fecha o de hora, específico. Veamos como podemos usar esta función en nuestros informes.

La tabla a la izquierda resume las ventas de los primeros 10 meses del año. La celda E2 contiene esta fórmula ="Total de ventas al presente: "&$C$13

Podemos ver que a pesar de que el valor en la celda C13 tiene formato numérico, en el texto de la celda E2 aparece sin formato. Para mostrar el texto con formato numérico usamos la función TEXTO que tiene dos argumentos: la referencia al valor y el formato a aplicarle. En nuestro caso 

="Total de ventas al presente: "&TEXTO($C$13,"#,##0")

Podemos ir un poco más lejos e incluir un cálculo dentro de la función

Aquí estamos anidando la función SUMA dentro de TEXTO. De la misma manera podemos usar la función PROMEDIO 

El argumento formato es la clave del formato tal como podemos ver en la lista de formatos numéricos

La clave del formato podemos copiarla de la ventanilla "Tipo" y pegarla en nuestra fórmula.

Algebra booleana y Excel (las funciones Y, O, XO, NOT)

Google celebra los 200 años del nacimiento de George Boole con este doodle

Mis avisados lectores habrán notado las funciones Excel AND (Y en la versión española de Excel), XOR (XO), OR (O) y NOT (NO).

George Boole es considerado el padre del álgebra booleana, una de las bases de la informática. Excel, por supuesto, permite realizar operaciones booleanas contando para eso con las funciones mencionadas (XO disponible en Excel 2013 y posterior).

Me voy a permitir celebrar también el acontecimiento con una breve reseña de estas funciones y algunos de sus usos.

Las funciones booleanas evalúan un número de condiciones y dan como resultado los valores lógicos VERDADERO o FALSO.

Función	Resultado	Observaciones
Y (AND)	VERDADERO si todos los argumentos se evalúan como VERDADERO; devuelve FALSO si uno o más argumentos se evalúan como FALSO.
O (OR)	VERDADERO si alguno de los argumentos es VERDADERO; devolverá FALSO si todos los argumentos son FALSOS.
XO (XOR)	Devuelve un O exclusivo lógico de todos los argumentos (ver el ejemplo más adelante).	Disponible a partir de Excel 2013
NO (NOT)	Invierte el valor lógico del argumento.

Estas funciones nos permiten combinar varias condiciones y evaluar si todas se cumplen, si algunas o ninguna.

En esta hoja muestro la evaluación de tres condiciones en una lista de nombres



Las funciones Y y O son intuitivas. La fórmula en la celda F8 es =Y(C8=$B$4,D8=$C$4,E8>$D$4); podemos ver que sólo una condición se cumple (mayor de 25) por lo que el resultado es FALSO (FALSE). En cambio el resultado en la celda G8, que contiene la fórmula =O(C8=$B$4,D8=$C$4,E8>$D$4), es VERDADERO (TRUE) ya que una de las condiciones se cumplen.

La función XO es menos intuitiva. Como regla general digamos que si el número de condiciones que se cumplen (VERDADERO) es impar el resultado es VERDADERO y si el número es par entonces el resultado es FALSO.
Para dar otro ejemplo del uso de XO supongamos que tenemos una lista de trabajadores donde se señalan los turnos trabajados, mañana y tarde. Si para saber quien ha trabajado jornada parcial usamos la fórmula =XO(C3,D3) en la tabla del ejemplo

Las funciones ESERR, ESERROR, ESNOD de Excel

Bajo la categoría Información encontramos en Excel varias funciones para administrar errores. Estas funciones evalúan si una celda contiene un valor de error. Estas funciones dan como resultado un valor VERDADERO o FALSO (funciones booleanas) y por lo general las usamos combinadas con la función SI.

• ESERROR: Valor se refiere a uno de los valores de error (#N/A, #¡VALOR!, #¡REF!, #¡DIV/0!, #¡NUM!, #¿NOMBRE? o #¡NULO!)

• ESERR: Valor se refiere a cualquier valor de error excepto #N/A.

• ESNOD: Valor se refiere al valor de error #N/A (el valor no está disponible).

A partir de Excel 2007 disponemos también de la función SI.ERROR.

A primera vista parece superfluo que Microsoft haya desarrollado tres funciones que hacer prácticamente lo mismo. Pero no todos los errores son iguales y de hecho, algunos de ellos no son errores en el sentido estricto de la palabra.

Consideremos el error #¡DIV/0! que nos indica que se ha tratado de dividir un número por cero. Esto es obviamente un error.

Consideremos ahora #N/A. Este "error" indica que el valor buscado no está disponible, como en este ejemplo

Las fórmulas en la columna F calculan la inversa (1/n) del valor correspondiente al color según la tabla B2:C8. Si el valor es cero, como en el caso de los colores blanco o negro, en lugar del error #DIV/0, queremos que aparezca 0. Lo logramos, por ejemplo, con la fórmula

=SI.ERROR(1/BUSCARV(F2,tblColorValor[#Datos],2,0),0)

Pero si el color no figura en la tabla, como en el caso de "Naranja", queremos que aparezca #N/A para saber que el color falta.

En este sentido tampoco #¡VALOR! o #¡NUM! son errores en todos los casos.

Otra función digna de mención es TIPO.DE.ERROR

Esat función da como resultado un número que corresponde a uno de los valores de error de Microsoft Excel o da el error #N/A si no existe ningún error. Combinada con la función SI nos permite dar resultados distintos, como un texto informativo, de acuerdo al tipo de error,

La función DELTA de Excel

Siguiendo con mi (inintencionada) serie "Funciones Excel poco usadas" hoy veremos la función DELTA().
En realidad no hay mucho que podamos decir sobre esta función. La definición es bien escueta: Prueba si dos números son iguales; si lo son, da como resultado 1, en caso contrario el resultado es 0

Podríamos afirmar que esta función es innecesaria ya que la simplísima fórmula =A1=A2 daría el mismo resultado (en realidad da VERDADERO en lugar de 1 y FALSO en lugar de 0). Sin embargo esta función tiene una ventaja sobre la comparación (la fórmula que mostramos más arriba): permite comparar números con textos que representan números o textos que representan números entre si.

La celda A1 contiene el número 31; la celda A2 contiene el texto 31 (nótese que va precedido de una comilla). La celda C2 contiene la fórmula =DELTA(A1,A2); la celda D2 contiene la fórmula =A1=A2.

Otra situación donde la comparación no nos sirve es cuando los valores a comparar son números como texto pero con espacios antes o después de las cifras (sitaución que suele darse cuando importamos datos de una base de datos a Excel).

En este ejemplo, la celda A2 contiene el texto 31 precedido de tres espacios; la celda A3 contiene 31 seguido por siete espacios. DELTA da 1, es decir, los considera iguales; la fórmula =A2=A3 da FALSO.

La función CELDA de Excel

En un post anterior sobre funciones Excel raramente usadas tendría que haber mencionado a la función CELDA(). Por ejemplo, a lo largo y a lo ancho de los 670 posts de esta blog, hay solamente dos menciones a esta función.

Tal como lo insinúa su nombre, esta función da como resultado información sobre una celda y su entorno. La función CELDA() acepta dos argumentos, el tipo de información, que es obligatorio y la celda de referencia, que es opcional.

El uso más obvio de esta función es en fórmulas donde el resultado depende de cierta característica del contenido de la celda o del entorno donde se halla.

Esta función tiene dos características importantes:

1. es volátil, es decir que es recalculada con cualquier cambio en la hoja. Por eso se suele recomendar como buena práctica evitar, en la medida de lo posible el uso de estas funciones. 
2. Si se omite la referencia a una celda la información especificada en el argumento tipo_de_info el resultado muestra la información de la última celda cambiada

Veamos algunos usos posibles.

Determinar si la celda contiene una fecha.

Excel no tiene una función nativa para determinar si el valor de una celda es una fecha (existe la función ESNUMERO pero no la función ESFECHA). Podemos usar la opción "Formato" para evaluar si el contenido es una fecha en lugar de usar la función IsDate de Vba (lo que implica crear una UDF).

Sucede que si la celda contiene una fecha la función CELDA con la opción Formato da como resultado "Dx", donde "x" es un número que identifica el tipo de formato

En este ejemplo, la fórmula =IZQUIERDA(CELDA("formato",B5),1)="D" nos permite determinar que la celda B5 contiene una fecha

SUBTOTALES con columnas ocultas 

Podemos hacerlo usando la opción "ancho" que da el ancho de la columna de referencia como número entero (SUBTOTALES realiza cálculos sin tomar en cuenta filas ocultas, pero no funciona con columnas ocultas). 

Si la columna esta oculta su ancho es 0, dato que podemor usar en conjunto con SUMAPRODUCTO tal como muestra Ismael Romero en esta nota de su blog Excelforo

El rango C1:H1 contiene la fórmula =CELDA("ancho",C4)<>0; la celda I4 contiene la fórmula =SUMAPRODUCTO(C1:H1*C4:H4).

Al multiplicar las matriz C1:H1 por la matriz C4:H4, SUMAPRODUCTO convierte los valores VERDADERO en 1 y los FALSO en 0.

Hay que tomar en cuenta que ocultar o mostrar filas no dispara el mecanismo de cálculo de Excel y por lo tanto hay que apretar F9 para actualizar el resultado de la fórmula.

Extaer el nombre de la hoja: 

Lo hacemos usando la opción "nombrearchivo" que da la  ruta de acceso completa de la celda referencia, en forma de texto.  

Supongamos que tenemos un cuaderno con una hoja por mes; si el nombre de la hoja es "agosto", esta fórmula dará como resultado "agosto"

=EXTRAE(CELDA("nombrearchivo",A1),ENCONTRAR("]",CELDA("nombrearchivo",A1))+1,256)

En esta fórmula es obligatorio usar la referencia a una celda de la hoja ya que de lo contrario elresultado será el nombre de la hoja donde se produjo el último cambio.

Cálculos de interés compuesto con Excel

En este post voy a tocar el tema del cálculo con Excel del interés compuesto. Supongo que la mayoría de mis lectores, en particular aquellos que ocupan su tiempo de trabajo con temas financieros, están familiarizados con el tema. Para quien no lo esté digamos, en pocas palabras, que el interés compuesto consiste en tomar en cuenta el interés acumulado en los períodos anteriores al calcular el interés de un período, o citando a Wikipedia

El interés compuesto representa la acumulación de intereses que se han generado en un período determinado por un capital inicial (CI) o principal a una tasa de interés (r) durante (n) periodos de imposición, de modo que los intereses que se obtienen al final de cada período de inversión no se retiran sino que se reinvierten o añaden al capital inicial, es decir, se capitalizan.

Este ejemplo muestra la aplicación de la definición

Por supuesto con Excel podemos ser un poco más sofisticados y hacer el cálculo en una única celda usando la expresión =C2*(1+C3)^C4 o con =C2*POTENCIA(1+C3,C4)

Pero podemos ser aún más sofisticados y usar la función VF (valor futuro) dejando el argumento Pago de la función en blanco

Hasta ahora hemos asumido una tasa de interés constante para todos los períodos. Supongamos que cada año tenemos una tasa de interés distinta. En este caso no podemos usar la función VF. Pero Excel viene provisto con 55 funciones financieras y una de ellas es VF.PLAN que nos permite hacer el cálculo fácilmente, como podemos ver en este ejemplo:

La función VF.PLAN tiene dos argumentos: el capital inicial y un rango que contiene las tasas de interés a tomar en cuenta que Excel usa como matriz para realizar el cálculo

Podemos darle dinamismo a nuestro modelo usando Tablas (como ya saben, soy fanático de las tablas). En nuestro ejemplo creamos la tabla "tblInteres" y la usamos en nuestra fórmula

De esta manera cuando agregamos o quitamos períodos la fórmula se ajusta automáticamente

Ahora surge la pregunta, si tenemos distintas tasas en distintos períodos ¿cómo calculamos la tasa promedio? Esta tasa es conocida como TCAC (Tasa de Crecimiento Anual Compuesto) también conocida como CAGR (en inglés Compound Annual Growth Rate).
Dados:

• V(t_0) : valor inicial, 
• V(t_n) : valor final, 
• t_n - t_0 : número de años

la expresión para calcular la TCAC (CAGR) es

En nuestro ejemplo calculamos la TCAC con la expresión   =(C3/C2)^(1/5)-1

Podemos también usar la función TASA, dejando el argumento Pago en blanco y poniendo el capital inicial como número negativo (o el valor futuro como negativo y el inicial como positivo)

Es una norma de buena práctica evitar el uso de constantes en nuestras fórmulas; por lo tanto en lugar de la constante 5 para el número de períodos usamos la fórmula CONTARA(tblInteres[Período]) quedando
=TASA(CONTARA(tblInteres[Período]),,-C2,C3)

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.