Calcular el mínimo con criterios excluyendo ceros

Un lector me consultaba cómo calcular el precio mínimo de los tornillos en esta lista

Para hacerlo tenemos que crear una matriz (array) de valores de la lista que correspondan a la categoría tornillos. Esta lista la usamos como argumento en la función MIN para obtener el valor buscado. La fórmula matricial sería

=MIN((A2:A13="Tornillos")*B2:B13)

Esta es una fórmula matricial que introducimos apretando simultáneamente Ctrl+Mayúscula+Enter.

El problema con esta fórmula es que cuando un valor en la lista no corresponde a la categoría buscada ("tornillos" en nuestro caso), el valor en la matriz es 0 (cero) y éste pasa a ser el valor mínimo (a excepción de que la lista contenga valores negativos).

La solución es usar la función SI para crear una condición que excluya los ceros. La fórmula matricial que excluye los ceros del cálculo del mínimo es

=MIN(SI((A2:A13="Tornillos"),B2:B13))

Nótese que excluimos el tercer argumento de la función SI, es decir, ponemos solamente el resultado a calcular cuando la condición se cumple.

Los precios máximos se calculan sin mucho trámite con esta función matricial

=MAX((A2:A13="Tornillos")*(B2:B13))

Ahora vamos a darle un toque un poco más profesional a la solución agregándole la posibilidad de elegir el tipo de artículo en forma dinámica. Para esto creamos una lista desplegable con validación de datos en la celda E3

Nuestro modelo se ve ahora así

donde hemos modificado las fórmulas de esta manera

=MAX((A2:A13=E3)*(B2:B13))

=MIN(SI((A2:A13=E3),B2:B13))

Ahora, si queremos realmente impresionar al jefe (o mejor aún, a la secretaria) hacemos que también el tipo de valor buscado se establezca dinámicamente.

Agregamos dos columnas a la izquierda de la columna A, donde haremos cálculos auxiliares

En la celda A7 ponemos esta fórmula

=COINCIDIR(G2,A3:A4,0)

En las celdas A9 y A10 ponemos las fórmulas de máximo y mínimo respectivamente.

En G2 ponemos una lista desplegable con validación de datos con dos posibilidades: Máximo y Mínimo.

Finalmente, en la celda G4 ponemos la fórmula

=INDICE(A9:A10,A7)

Ahora, cuando el usuario elige el máximo, la celda A7 recibe el valor 1; si elige el mínimo la celda muestra el 2. Este valor es usado como argumento en la función INDICE de la celda G4, que extrae el valor de la celda A9 o A10, según el caso.

El último toque es ocultar la columna A.

El archivo con el ejemplo se puede descargar aquí.

Las nuevas funciones DIA.LAB y DIAS.LAB en Excel 2010

Esta última semana se ha caracterizado por una intensa actividad, tratando de terminar a tiempo algunos proyectos. La semana entrante no pinta mejor, por lo que no me queda más que pedir paciencia a todos los que me han consultado por mail en los últimos días. Poco a poco iré poniéndome al día.

Mientras tanto, una entrada “al paso”. Hace poco menos de dos años atrás publiqué dos entradas mostrando alternativas a las funciones DIA.LAB y DIAS.LAB de Excel. El problema con estas funciones consistía en que tenían pre-programado el fin de semana con los días sábado y domingo.

Quien haya instalado Excel 2010 puede olvidarse de las soluciones que proponía en esas notas y usar las nuevas funciones DIA.LAB.INTL y DIAS.LAB.INTL

Estas funciones cuentan con un argumento opcional “fin de semana”

DIA.LAB.INTL(fecha_inicial; días_lab; [fin_de_semana]; [festivos])

DIAS.LAB.INTL(fecha_inicial; fecha_final; [fin_de_semana]; [festivos])

Este argumento nos permite establecer cuáles son el o los días a tomar en cuenta como fin de semana. Lo interesante es que este argumento tiene dos sintaxis alternativas:

1 – Podemos usar un valor de esta tabla de valores (ver la ayuda en línea de Excel)

2 – usar una combinación de siete ceros y unos. La posición de cada cifra en la cadena representa el día de la semana, empezando por el lunes. Usamos el 1 para representar los días no laborables y 0 para los laborales. Así, por ejemplo, si el fin de semana es viernes-sábado, la cadena será “0000110”; si el fin de semana es domingo-lunes la cadena será “1000001”; y si alguien conoce el país donde el fin de semana es “1111111”, por favor me avisa donde queda el consulado o la oficina de inmigraciones….

Suma interna de los dígitos de un número con Excel - ampliación

En mi nota anterior sobre la suma interna de los dígitos de un número, propuse esta fórmula para la tarea

=SUMAPRODUCTO(EXTRAE(A2,FILA(INDIRECTO("1:" & LARGO(A2))),1)*1)

También podemos usar

=SUMAPRODUCTO(--EXTRAE(A2,FILA(INDIRECTO("1:" & LARGO(A2))),1))

si preferimos usar la doble negación para convertir los valores VERDADERO o FALSO en 1 o 0.

El problema con esta fórmula es que cuando el resultado es un número de dos dígitos, tenemos que volver a aplicarla, ya sea anidando dos fórmulas o usando celdas auxiliares.

Con esta fórmula podemos realizar la suma interna de los dígitos de un número en una única operación:

=1+RESIDUO(SUMAPRODUCTO(--EXTRAE(A2,FILA(INDIRECTO("1:" & LARGO(A2))),1))-1,9)

Esta fórmula se base en el artificio matemático "Prueba del nueve" y fue propuesta por Rick Rothstein en un comentario en el sitio de Chandoo.