Calcula el Coste Promedio Operativo por Tipo y Condición en Excel

En el análisis de datos, es fundamental poder extraer información precisa y consolidada. Una tarea común es calcular promedios basados en múltiples condiciones, especialmente cuando se trata de informes financieros o de rendimiento. Esta fórmula anidada combina la potencia de PROMEDIO.SI.CONJUNTO para filtrar y promediar datos con múltiples criterios, y la utilidad de REDONDEAR para presentar el resultado con un formato numérico limpio y estandarizado, generalmente con dos decimales para valores monetarios.

El uso conjunto de estas funciones permite, por ejemplo, obtener el coste promedio de todas las operaciones de un tipo específico que ya han sido completadas, excluyendo las que están pendientes o en curso. Este cálculo es vital para la toma de decisiones estratégicas, la evaluación de la rentabilidad y el control de costes en cualquier proyecto o departamento.

Sintaxis

=REDONDEAR(PROMEDIO.SI.CONJUNTO(rango_promedio, rango_criterios1, criterio1, [rango_criterios2, criterio2], ...), num_decimales)

Esta fórmula se compone de dos funciones principales que trabajan en conjunto. La función interna, PROMEDIO.SI.CONJUNTO, realiza el cálculo principal, y su resultado es procesado por la función externa, REDONDEAR.

• REDONDEAR(…): Es la función externa. Su propósito es tomar un número y redondearlo a un número específico de decimales. En este caso, el número que recibe es el resultado del promedio condicional.
• PROMEDIO.SI.CONJUNTO(…): Es la función interna que calcula el promedio de un rango de celdas que cumplen con una o más condiciones.
  • rango_promedio: El rango de celdas que contiene los valores numéricos que se desean promediar (por ejemplo, una columna con costes).
  • rango_criterios1: El primer rango de celdas que se evaluará según el primer criterio (por ejemplo, una columna con tipos de operación).
  • criterio1: La condición que deben cumplir las celdas en el rango_criterios1 para ser incluidas en el promedio (por ejemplo, «Logística» o la referencia a una celda que contenga ese texto).
  • [rango_criterios2, criterio2], …: Pares adicionales de rangos y criterios. Se pueden añadir hasta 127 pares para refinar el cálculo. Todos los rangos de criterios deben tener las mismas dimensiones que el rango_promedio.
• num_decimales: Argumento de la función REDONDEAR. Especifica a cuántos decimales se debe redondear el resultado del promedio. Para costes, se suele usar el valor 2.

Ejemplos

Imaginemos que gestionamos un registro de operaciones en una hoja de cálculo. La tabla contiene el tipo de operación, su estado y el coste total asociado.

Ejemplo 1: Criterios fijos Para calcular el coste promedio de todas las operaciones de «Logística» que han sido «Completada», usamos los valores directamente en la fórmula. La fórmula buscará en la columna B las celdas que contengan «Logística» y en la columna C las que contengan «Completada», y promediará los valores correspondientes de la columna D.

=REDONDEAR(PROMEDIO.SI.CONJUNTO(D2:D8, B2:B8, "Logística", C2:C8, "Completada"), 2)

Resultado esperado: 165,61 €. (Calculado como el promedio de 150,55, 180,47 y 165,80)

Ejemplo 2: Criterios dinámicos con referencias a celdas Es una mejor práctica usar referencias a celdas para los criterios. Esto permite cambiar los valores en las celdas de criterio sin tener que modificar la fórmula. Supongamos que en la celda F2 escribimos «Marketing» y en G2 «Completada».

=REDONDEAR(PROMEDIO.SI.CONJUNTO(D2:D8, B2:B8, F2, C2:C8, G2), 2)

Resultado esperado: 230,38 €. (Calculado como el promedio de 210,00 y 250,75)

Ejemplo 3: Uso de operadores lógicos La fórmula también admite operadores lógicos en los criterios. Por ejemplo, para calcular el coste promedio de todas las operaciones completadas que no son de «Logística».

=REDONDEAR(PROMEDIO.SI.CONJUNTO(D2:D8, B2:B8, "<>Logística", C2:C8, "Completada"), 2)

Resultado esperado: 182,00 €. (Calculado como el promedio de 210,00, 85,20 y 250,75)

Aplicaciones Prácticas

• 1Análisis de Rentabilidad: Calcular el coste promedio de los proyectos finalizados por categoría para identificar cuáles son más o menos costosos de ejecutar.
• 2Gestión de Inventario: Determinar el precio de venta promedio de productos de una marca específica que tienen un stock por debajo de un umbral determinado.
• 3Recursos Humanos: Calcular el salario promedio de los empleados de un departamento que superan una cierta antigüedad en la empresa.
• 4Informes de Ventas: Obtener el valor promedio de las ventas cerradas por un agente comercial en una región específica.

Observaciones

Es crucial que todos los rangos de criterios (rango_criterios1, rango_criterios2, etc.) y el rango_promedio tengan exactamente el mismo número de filas y columnas. De lo contrario, la fórmula devolverá un error.

La función PROMEDIO.SI.CONJUNTO no distingue entre mayúsculas y minúsculas en los criterios de texto. Por ejemplo, «Completada» producirá el mismo resultado que «completada» o «COMPLETADA».

Si ninguna celda cumple con todos los criterios especificados, PROMEDIO.SI.CONJUNTO intentará dividir entre cero, lo que resulta en un error.

Errores comunes

• #¡DIV/0!: Ocurre cuando ningún conjunto de celdas cumple con todos los criterios. Esto significa que no hay datos que promediar, y la función intenta realizar una división por cero.
• #¡VALOR!: Se produce si los rangos proporcionados (rango_promedio y los rangos de criterios) no tienen las mismas dimensiones.

Alternativas

Aunque la combinación de REDONDEAR y PROMEDIO.SI.CONJUNTO es la más directa y eficiente para este propósito, existen otras formas de lograr un resultado similar, especialmente en versiones más recientes de Excel.

• =REDONDEAR(PROMEDIO(FILTRAR(rango_costes, (rango_tipo="Tipo_X") * (rango_estado="Completada"))), 2)

  En versiones de Excel con matrices dinámicas (Excel 365, Excel 2021), se puede usar la función FILTRAR para aislar primero los costes que cumplen las condiciones y luego calcular su promedio con la función PROMEDIO. Este método es a menudo más fácil de leer y depurar.

• {=REDONDEAR(PROMEDIO(SI((rango_tipo="Tipo_X") * (rango_estado="Completada"), rango_costes)), 2)}

  Esta es una fórmula matricial tradicional, necesaria en versiones antiguas de Excel. Realiza una comprobación lógica para cada fila y promedia solo los valores que cumplen ambas condiciones. Requiere ser introducida pulsando Ctrl + Mayús + Intro. En versiones modernas, esta sintaxis funciona sin la combinación de teclas especial.