El promedio es una de las operaciones más empleadas en Excel y en ocasiones necesitamos calcular el promedio con varias condiciones. Afortunadamente, a partir de la versión de Excel 2007 contamos con la función PROMEDIO.SI.CONJUNTO que facilita en gran manera este tipo de cálculos.
Promedio con una condición
Comencemos por analizar el ejemplo más sencillo utilizando una sola condición. En una hoja de Excel tengo los datos sobre las ventas de los últimos 3 meses y deseo obtener el monto de venta promedio de Roberto que es uno de los vendedores. El promedio de calculará sobre la columna Monto, pero la condición está en la columna Vendedor que tiene los nombres de los vendedores. Para obtener el promedio utilizamos la siguiente fórmula:
=PROMEDIO.SI.CONJUNTO(E2:E13,B2:B13,"Roberto")
El primer argumento es el rango que contiene los datos a utilizar para calcular el promedio. El segundo argumento es el rango sobre el cual aplicaremos la condición y el tercer argumento es el criterio que se debe cumplir. Observa el resultado al aplicar esta fórmula sobre los datos:
Ya que en este ejemplo solamente hemos utilizado un criterio pudimos haber encontrado el mismo resultado utilizando la función PROMEDIO.SI de la siguiente manera:
=PROMEDIO.SI(B2:B13,"Roberto",E2:E13)
El orden de los argumentos de la función PROMEDIO.SI es un tanto diferente a nuestro ejemplo anterior, pero obtendremos el mismo resultado. La diferencia entre ambas funciones es que la función PROMEDIO.SI solamente permite indicar un solo criterio mientras que la función PROMEDIO.SI.CONJUNTO nos permitirá indicar varios criterios.
Promedio con varias condiciones
Utilizando los datos del ejemplo anterior, ahora deseo obtener el promedio de las ventas realizadas por Roberto en la zona sur. Ya que necesito que se cumplan dos condiciones, debo emplear la función PROMEDIO.SI.CONJUNTO de la siguiente manera:
=PROMEDIO.SI.CONJUNTO(E2:E13,B2:B13,"Roberto",D2:D13, "Sur")
La diferencia con el primer ejemplo donde teníamos una sola condición es que, como tercer argumento he colocado el rango donde se encuentra la zona (D2:D13) y como cuarto argumento la condición que debe cumplir. El resultado es el siguiente:
Si por el contrario quisiéramos obtener el promedio de todas las ventas de iPad en la zona norte, entonces nuestra fórmula debe cambiar de la siguiente manera:
=PROMEDIO.SI.CONJUNTO(E2:E13,C2:C13,"iPad",D2:D13, "Norte")
En lugar de considerar la columna con los nombres de vendedores, ahora consideramos la columna con los nombres de producto. El promedio obtenido será diferente al considerar estos nuevos criterios:
En ambos ejemplos he utilizado solamente dos criterios pero puedes utilizar la función PROMEDIO.SI.CONJUNTO con todos los criterios que necesites hasta un máximo de 127 criterios en Excel 2010.
Números como criterio
Hasta ahora hemos aplicado los criterios sobre columnas que contienen cadenas de texto, pero es totalmente factible utilizar criterios sobre columnas con números. Supongamos ahora que deseo obtener el promedio de las ventas mayores a 200 en la zona sur. Observa con detenimiento los últimos dos argumentos de esta nueva fórmula:
=PROMEDIO.SI.CONJUNTO(E2:E13,D2:D13, "Sur",E2:E13,">200")
Cuando utilizamos columnas numéricas, como es el caso de la columna Monto, podemos utilizar operadores de comparación en nuestro criterio. El resultado de esta fórmula será el siguiente:
Fechas como criterio
También es posible aplicar un criterio sobre una columna que contiene fechas, Por ejemplo, para obtener el promedio de todos los valores con una fecha superior al 01 de febrero del 2013 podemos utilizar la siguiente fórmula:
=PROMEDIO.SI.CONJUNTO(E2:E13,A2:A13,">01/02/2013")
Como tal vez lo sabes, una fecha en Excel es en realidad un número. Si este concepto es nuevo para ti, entonces te sugiero leer el artículo Fechas en Excel antes de continuar, especialmente la sección que muestra cómo obtener el valor numérico de una fecha.El hecho de que una fecha sea en realidad un valor numérico me permitirá mostrar otra alternativa de solución cuando queremos tener una fecha como criterio.
Ya que en estricto sentido podemos tratar una columna de fechas como cualquier otra columna de números, podremos crear también nuestros criterios de fechas como criterios numéricos. Si deseo obtener el promedio de ventas durante el mes de febrero, entonces debo obtener el valor numérico para el 31 de enero del 2013 y para el 1 de marzo del 2013 y obtener el promedio de las fechas intermedias.
- Valor numérico para el 31 de enero del 2013: 41305
- Valor numérico para el 01 de marzo del 2013: 41334
Una vez que tenemos los valores numéricos de las fechas en ambos límites podremos crear nuestra fórmula con la función PROMEDIO.SI.CONJUNTO de la siguiente manera:
=PROMEDIO.SI.CONJUNTO(E2:E13,A2:A13,">41305",A2:A13,"<41334")
Esta fórmula aplica dos criterios sobre una misma columna para considerar solamente los montos que se encuentran entre ambas fechas, es decir, el mes de febrero. El resultado de esta fórmula es el siguiente:
Dos condiciones sobre un mismo rango
En el ejemplo anterior apliqué dos criterios sobre un mismo rango, pero es importante comprender la lógica de esto para evitar cualquier error así que analizaremos con un poco más de detenimiento la fórmula anterior. El primer criterio utilizado (“>41305”) fue indicado para incluir los valores con una fecha mayor al 31 de enero del 2013.
Si solo hubiéramos utilizado este criterio habríamos obtenido el promedio de los meses de febrero y marzo pero el segundo criterio (“<41334”) obliga a la función a aplicar otro “filtro” y considerar solamente los valores con una fecha menor a la del 01 de marzo. De esta manera, cada fecha es evaluada para saber si cumple con ambas condiciones y en caso positivo el valor es considerado en el cálculo del promedio.
Es muy importante recordar este principio y saber que la función evaluará cada valor para saber si cumple ambas condiciones. Es fácil olvidarse de esta lógica y cometer algunos errores con la función PROMEDIO.SI.CONJUNTO. Por ejemplo, algunos usuarios de Excel intentan utilizar la función para encontrar el promedio de las ventas hechas por Roberto y por Brenda con una fórmula como la siguiente:
=PROMEDIO.SI.CONJUNTO(E2:E13,B2:B13,"Roberto",B2:B13,"Brenda")
Se piensa incorrectamente que la función obtendrá los valores que corresponden a Roberto y los valores que corresponden a Brenda y obtendrá el promedio. Sin embargo esta fórmula nos devuelve un error:
Recuerda que la función evaluará cada valor para saber si cumple con ambas condiciones. En este caso, ninguna celda tiene el valor Roberto y al mismo tiempo el valor Brenda por lo que no se encuentra ningún registro y se hace el promedio dividiéndolo entre cero y por tal motivo obtenemos el error #¡DIV/0!. La única alternativa para obtener el promedio de las ventas hechas por Roberto y por Brenda es utilizar dos funciones PROMEDIO.SI (o también PROMEDIO.SI.CONJUNTO) de la siguiente manera:
=PROMEDIO(PROMEDIO.SI(B2:B13,"Roberto",E2:E13),PROMEDIO.SI(B2:B13,"Brenda",E2:E13))
El primer argumento de la función PROMEDIO es precisamente el promedio de las ventas hechas por Roberto y el segundo argumento el promedio de las ventas hechas por Brenda. De esta manera obtenemos el promedio para cada vendedor con la función PROMEDIO.SI y posteriormente el promedio entre ambos con la función PROMEDIO.
No olvides que al utilizar la función PROMEDIO.SI.CONJUNTO e indicar varias condiciones sobre una misma columna, la función evaluará cada valor para saber si cumple con todas las condiciones establecidas.
Artículos relacionados
Sumar y contar con varias condiciones en Excel