Promedio en Excel con más de una condición

Dos lectores me consultan sobre cómo calcular con Excel el promedio de una serie de valores, sujeto a más de una condición. Uno de ellos me pide que lo muestre sin utilizar funciones Base de Datos como BDPROMEDIO. Cosa entendible ya que el uso de estas funciones es un tanto engorroso.

La técnica que sugiero es la que ya había mostrado en mi nota Sumar y contar con varias condiciones en Excel:SUMAR.SI, CONTAR.SI y SUMAPRODUCTO.

Definimos un nombre que contenga una lista de valores de un rango en una hoja de cálculo. Llamamos a este nombre "valores". En una celda ponemos el valor que será la cota inferior (nombre = "cota_inferior) y en otra la cota superior ("cota_superior").




Para sumar todos los valores comprendidos entre la cota superior y la inferior usamos la fórmula

=SUMAPRODUCTO((valores<=cota_superior)*(valores>=cota_inferior)*valores)

Para contar la cantidad de valores comprendidos entre las cotas usamos

=SUMAPRODUCTO((valores<=cota_superior)*(valores>=cota_inferior))

Para calcular el promedio podemos dividir entre las celdas que contienen los resultados o combinar ambas fórmulas en una sola

=SUMAPRODUCTO((valores<=cota_superior)*(valores>=cota_inferior)*valores)/SUMAPRODUCTO((valores<=cota_superior)*(valores>=cota_inferior))

Luego podemos usar Formato Condicional para señalar los valores de la lista usados en el cálculo



Nuestra hoja se vería así




El archivo con el ejemplo de promedio con más de una condición se puede descargar apretando el enlace.

Technorati Tags: MS Excel

Función números a letras en Excel en español – nueva versión

La función Excel Números a Letras en Español que publiqué en la última entrada sobre el tema, estaba orientada a usos financieros. Es decir, presuponía que siempre queríamos expresar números en forma literal acompañados por la correspondiente moneda.
Debido a esto todos los argumentos de la función eran obligatorios. Si no introducíamos algún valor para la variable "Sep" (separador de decimales) o para "Mon" (moneda), el resultado de la función era el error #VALOR!.

La versión que publico en esta entrada esta basada en la función que Bernie Deitrick presentó en uno de los foros Excel hace varios años atrás. Esta función es mucho más compacta y sus argumentos son opcionales, exceptuando el valor que se quiere convertir, por supuesto.

Para poder adaptar la función a nuestra querida lengua castellana he tenido que trabajar duro. Las cosas son más sencillas en ingles:

100 es one hundred en inglés pero sólo cien en castellana y no "uno cien"
500 es five hundred, respetando el patrón, pero en castellano diremos quinientos
1000 será one thousend, pero en castellano simplemente mil y no "uno mil"

Pero finalmente creo haber superado todas las dificultades, hasta que mis lectores empiecen a encontrar las fallas!

La función, que pueden descargar aquí, tiene cuatro argumentos:





N: el número que queremos convertir a letras (mandatario)

Ccy: el nombre de la moneda (opcional)

Cents: el nombre de las fracciones de la moneda (opcional)

Join: por defecto es "con", pero podemos sustituirla con "y" por ejemplo

Fraction: acepta dos valores FALSE (0) o TRUE (1). Cuando ponemos TRUE o 1 los centavos son expresados como fracción (67/100 centavos). Por defecto es FALSE




Technorati Tags: MS Excel

Encontrar el mínimo entre valores repetidos en un rango en Excel.

Este blog trata sobre temas de interés general para los usuarios de Excel. Cuando recibo preguntas o consultas sobre problemas específicos, suelo dirigir a mis lectores a los foros de ayuda, como Exceluciones por ejemplo, o trato el problema por línea privada (a través del mail), si es que dispongo del tiempo.
A veces sucede que un problema específico deriva a otro de interés más general. Así han nacido no pocas de las entradas en este blog.

Supongamos esta situación(como siempre, el archivo del ejemplo esta a vuestra disposición aquí):

- en el rango A2:A16 tengo 15 valores únicos
- en el rango B2:B16 tengo 15 valores, algunos repetidos

Quiero encontrar el mínimo entre los números repetidos en B2:B16 y encontrar su ubicación en el rango de los valores únicos (A2:A16):

En esta entrada veremos dos soluciones:
- usando columnas auxiliares
- usando fórmulas matriciales

La solución, en ambos casos, consiste en generar una matriz que contenga sólo los valores repetidos en la columna B, y luego buscar el mínimo entre los miembros de esta matriz. Una vez hallado el valor mínimo en B, podemos encontrar su "dirección" (la celda donde se encuentra) usando las funciones DIRECCION y COINCIDIR.

La solución con columnas auxiliares comienza por generar una columna auxiliar en el rango C2:C15 con esta fórmula =CONTAR.SI($B$2:$B$16;B2)>1 que copiamos a todo el rango. Esta fórmula da FALSO si un número no esta repetido en el rango, y VERDADERO si lo está



Luego multiplicamos los valores del rango C2:C16 por los del rango B2:B16. Al multiplicar por FALSO el resultado será 0. VERDADERO es interpretado por Excel como 1 y por lo tanto el resultado al multiplicar por VERDADERO será idéntico al valor en la celda correspondiente de la columna B



Podemos ver que en la columna D los números que no se repiten en B han sido transformados en 0.

Para encontrar el menor de ellos, sin tomar en cuenta los ceros, usamos esta fórmula:

=K.ESIMO.MENOR(D2:D16;CONTAR.SI(D2:D16;0)+1)

La función K.ESIMO.MENOR (nombre espeluznante! en inglés se llama SMALL) tiene dos argumentos. EL primero es el rango dónde buscamos el menor de los miembros; el segundo representa la posición a partir de la cual buscar el menor. En nuestra matriz hay 6 ceros, por lo tanto queremos empezar a buscar de la séptima posición. Para determinar este número en forma dinámica usamos CONTAR.SI para determinar cuantos ceros hay y al resultado le agregamos 1



La solución con fórmulas matriciales es conceptualmente igual, pero las "columnas auxiliares" pasan a ser matrices generadas dentro de la fórmula. Con fórmulas matriciales resolvemos el problema con una única fórmula

=K.ESIMO.MENOR((B2:B16*(CONTAR.SI($B$2:$B$16;B2:B16)>1));CONTAR(SI((B2:B16*(CONTAR.SI($B$2:$B$16;B2:B16)>1))=0;1))+1)

Como toda fórmula matriciales la anotamos apretando simultáneamente Ctrl+Mayúsculas+Enter.

Si comparamos esta fórmula con la de la solución con columnas auxiliares, veremos que de hecho son similares. La expresión D2:D16 es reemplazada en la fórmula matricial por la expresión B2:B16*(CONTAR.SI($B$2:$B$16;B2:B16)>1).

La expresión CONTAR.SI(D2:D16;0)+1 es reemplazada por CONTAR(SI((B2:B16*(CONTAR.SI($B$2:$B$16;B2:B16)>1))=0;1))+1

Aquí notarán seguramente que estamos usando una combinación de CONTAR y SI y no la función CONTAR.SI, como sería de esperar. Si intentáramos usar CONTAR.SI recibiríamos un aviso de error. De acuerdo a la ayuda on-line de Microsoft esto se debe a que las funciones SUMIF(), COUNTIF() y COUNTBLANK() utilizan el mismo algoritmo criteria-matching como funciones de base de datos tal como DSUM(). Este algoritmo no admite matrices.
Debido a esto usamos el rodeo =CONTAR(SI(...

Finalmente, para ubicar la posición del resultado en el rango de la columna A usamos esta fórmula:

=DIRECCION(COINCIDIR(C19;A1:A16;0);1)

donde C19 es la celda que contiene la fórmula matricial.

La pregunta de mi lector era en realidad a la inversa. Encontrar en el rango de los números repetidos, el mínimo (y el máximo) del rango de los valores únicos. Para esto nos basta con encontrar el mínimo con =MIN(A2:A16) y usar este resultado como argumento en la función DIRECCION

=DIRECCION(COINCIDIR(MIN(A2:A16);B2:B16;0)+1;2)

Si el número está repetido en el rango B, siempre recibiremos la dirección de la primera ocurrencia del número.

Otra alternativa interesante es usar Formato Condicional, para marcar todas las celda en el rango B que contienen el mínimo del rango A:



En esta formula hay que prestar atención a las direcciones absolutas y relativas. Por defecto, Formato Condicional usa direcciones absolutas.

Technorati Tags: MS Excel