Celdas promedio basadas en múltiples criterios en Excel

Promediar un número que expresa el valor central o típico de un conjunto de datos, en particular la moda, la mediana o (más comúnmente) la media, que se calcula dividiendo la suma de los valores del conjunto por su número. La fórmula básica para el promedio de n números x ₁ ,x ₂ ,……x _n es

A = (x1 + x2 ........xn)/ n

En Excel, hay una función promedio para este propósito, pero también se puede calcular manualmente usando funciones SUMA y CONTAR como esta

= SUM(D1:D5)/COUNT(D1:D5) 

Aquí

1. SUMA: esta función se usa para encontrar la suma de valores en el rango requerido de celdas.
2. CONTAR: Esta función se usa para obtener el conteo del rango requerido de celdas que contienen valores numéricos.

Función PROMEDIO

Excel proporciona una función directa llamada PROMEDIO para calcular el promedio (o la media) de los números en el rango especificado en el argumento de la función. En esta función, se pueden dar un máximo de 255 argumentos (números/referencias de celda/rango/arrays o constantes).

Sintaxis:

= AVERAGE(Num1, [Num2], ...)

Aquí

1. Num1 [Obligatorio]: Proporcione rango, referencias de celda o el primer número para calcular el promedio.
2. Num2,… [Opcional]: Proporcione números adicionales o un rango de referencias de celdas para calcular el promedio.

Ejemplo:

AVERAGE(C1:C5) 

Esto dará el promedio de valores numéricos en el rango C1 a C5.

Nota: En los métodos mencionados anteriormente, no se puede mencionar ningún criterio para calcular el promedio. Para este Excel, proporcione otra función llamada AVERAGEIF.

Células promedio basadas en un criterio

Excel proporciona una función directa llamada AVERAGEIF para calcular el promedio (o la media) de los números en el rango especificado que cumple con los criterios específicos especificados en el argumento de la función.

Sintaxis:

= AVERAGEIF(Range_Cells_For_Criteria, Criteria, [Range_Cells_For_Average])

Aquí

1. Range_Cells_For_Criteria [Obligatorio]: Proporcione el rango de celdas en el que se probarán los criterios.
2. Criterios [Obligatorio]: esta es la condición sobre la cual se deciden las celdas para promediar. Los criterios se pueden dar en forma de expresión (lógica), valor de texto o número, o referencia a una celda, por ejemplo, 100 o “>100” o “Apple” o A7.
3. Range_Cells_For_Average [Opcional]: especifique las celdas en las que se debe calcular el promedio. Range_Cells_For_Criteria se usa para encontrar el promedio si no está incluido.

Nota:

1. Una celda vacía en rango_promedio se ignora aquí.
2. Las celdas en el rango que contienen VERDADERO o FALSO se ignoran aquí.
3. Si el rango está en blanco, devuelva el #DIV0! Valor de error.
4. La celda vacía en los criterios se trata como 0.

Uso de comillas dobles («») en los criterios

En Excel, los valores de texto están entre comillas dobles («»), pero los números enteros no lo están, en general. Pero cuando se usan números en criterios con un operador lógico, el número y el operador también deben estar entre comillas. 

Ejemplos:

1. Para calcular el promedio de solo números positivos excluyendo 0 en el rango A2 a A6
= PROMEDIO SI (A2: A6, «> 0») es correcto.
= PROMEDIO.SI(A2:A6,>0) es incorrecto ya que el operador lógico debe incluirse entre comillas dobles.

2. Para obtener la edad promedio (EDAD está en la columna C) de los empleados que viven en la ciudad que tienen id = 1 (CityId está en la columna D)
= PROMEDIO SI (D2: D10, «= 1», C2: C10) es correcto
= PROMEDIO.SI(D2:D10, 1, C2:C10) también es correcto ya que aquí también se comprobará el valor 1 de las celdas D2 a D10.

3. Para obtener la edad promedio (EDAD está en la columna C) de los empleados que viven en una ciudad llamada Mumbai (La ciudad está en la columna B)
= PROMEDIO.SI(B2:B10, “Mumbai”, C2:C10) es correcto 
= PROMEDIO.SI( B2:B10, Mumbai, C2:C10) es incorrecto ya que el texto debe incluirse entre comillas dobles.

Ejemplo 1: Para calcular el promedio de solo números positivos excluyendo 0 en el rango A2 a A6.

Solución: En este ejemplo, se da un único criterio: “>0″. Entonces, todos los números en el rango que son mayores que 0 entran en el criterio. El promedio se calculará como

(12.7 + 87.2 + 100) / 3 = 66.66666667

Entonces, la fórmula promedio para el ejemplo anterior es

AVERAGEIF(A2:A6,">0")

Ejemplo 2: Para obtener la edad promedio (EDAD está en la columna C) de los empleados que viven en una ciudad llamada Mumbai (Ciudad está en la columna B).

Solución: dividamos el ejemplo en diferentes partes para que las partes individuales puedan sustituirse en la función PROMEDIO.SI

• Criterios: “Mumbai”.
• Celdas para criterios: B2 a B6.
• Celdas para promedio: C2 a C6 que cumplen con los criterios.

La edad promedio de los empleados que viven en Mumbai

= (65 + 45) /2 = 55

Entonces, la fórmula promedio para este ejemplo es

AVERAGEIF(B2:B6,"Mumbai",C2:C6)

Células promedio basadas en criterios múltiples

Excel proporciona una función directa llamada AVERAGEIFS para calcular el promedio (o la media) de los números en el rango especificado que cumple varios criterios especificados en el argumento de la función.

Sintaxis:

AVERAGEIFS(RangeForAverage,RangeForCriteria1,Criteria1,
           [RangeForCriteria2, Criteria2],..)

Aquí

1. RangeForAverage [Obligatorio]: proporcione el rango de celdas en el que se debe calcular el promedio.
2. RangeForCriteria1: Rango sobre el que se aplica criterio1.
3. Criteria1: el primer criterio que se aplica en RangeForCriteria1.

Nota:

1. Todos los rangos son obligatorios junto con sus criterios.
2. El criterio 1 es obligatorio, el resto son opcionales.

Ejemplo 1: Para calcular la edad promedio de los empleados que viven en Mumbai cuyas edades son > 50.

Solución: Los siguientes criterios se utilizan para resolver el problema

• RangeForAverage: C2 a C8.
• Criterio 1: “Mumbai”.
• RangeForCriteria1: B2 a B8.
• Criterio 2: “>50”.
• RangeForCriteria1: C2 a C8.

Aquí, el promedio se calcula sobre la base de dos criterios: empleados que viven en Mumbai y empleados cuya edad es> 50

AVERAGEIFS(C2:C8,B2:B8,"Mumbai",C2:C8,">50")

Ejemplo 2: Para obtener el precio promedio del producto con ID de categoría 1 y con un precio superior a Rs 500/-.

Solución: Los siguientes criterios se utilizarán para la función PROMEDIO.SI

• RangeForAverage: C2 a C11.
• Criterio 1: “ =1”.
• RangeForCriteria1: B2 a B11.
• Criterio 2: “>500”.
• RangeForCriteria2: C2 a C11.

Aquí, el promedio se calcula sobre la base de dos criterios: producto con ID de categoría 1 y precio superior a 500

AVERAGEIFSC2:C11,B2:B11,"=1",C2:C11,">500")

Usar valores de otra celda

La concatenación de texto se puede utilizar para utilizar el valor de otra celda. La concatenación es el proceso de conectar dos o más valores para formar una string de texto. Hay dos formas de concatenar las strings en Excel

1. Se puede utilizar el operador lógico &.
2. Función Concatenar en Excel para la concatenación de dos textos.

Ejemplo: para obtener calificaciones promedio (las calificaciones están en la columna B) de los estudiantes aprobados que tienen un valor mínimo de calificaciones para aprobar contenido en la celda D2.

Solución: En este ejemplo, el operador lógico & se utilizará para concatenar las dos condiciones para encontrar el resultado.

• Marcas mayores que 0.
• Marcas mínimas para aprobar, es decir, 10.

Aquí, D2 tiene un valor de 10, por lo que las calificaciones mínimas para aprobar son 10, se utilizarán los siguientes criterios: “>=” y D2. Esto concatenará “>=” y el valor contenido en la celda D2, es decir, 10. Después de la concatenación, los criterios se convertirán en “>=10”.

Entonces, en este ejemplo

• RangeForAverage- B2 a B12.
• Criterio1- Calificaciones mayores a 0.
• RangeForCriteria1- B2 a B12.

La siguiente fórmula se utilizará para calcular el promedio

=AVERAGEIFS(B2:B12,B2:B12,">=0"&D2)

comodines

Excel proporciona caracteres comodín que se pueden usar en los criterios. Algunos comodines son

1. * (asterisco): este símbolo se puede utilizar para representar cualquier número de caracteres.
2. ? (signo de interrogación): este símbolo se puede utilizar para representar un solo carácter.
3. ~ (tilde): este símbolo se usa antes de los dos comodines anteriores y también se tilde para que cuando sea necesario hacer coincidir un texto que contenga * o? O ~, Excel no los trata como comodines. Para tratarlos como caracteres normales en los criterios de Excel, se usa la tilde antes de ellos.

Ejemplos: Están los nombres de los productos en la columna A (De A1 a A10) y su respectivo precio en la columna C.

1. AVERAGEIFS(C2:C11, A:A11, “*a*”): Para encontrar el precio promedio de todos los productos que tienen el carácter ‘a’ en cualquier parte de su nombre.
2. AVERAGEIFS(C2:C11, A:A11, “?a*”): Para encontrar el precio promedio de todos los productos que tienen el segundo carácter ‘a’ en su nombre.
3. AVERAGEIFS(C2:C11, A:A11, “?aree”): Para encontrar el precio promedio de todos los productos que tienen un solo carácter antes de ‘aree’ en su nombre.
4. AVERAGEIFS(C2:C11, A:A11, ” ~*Sari “): Para encontrar el precio promedio de todos los productos que tienen un nombre es *Sari.

Ejemplo: Para encontrar el precio promedio de todos los productos que tienen el carácter ‘a’ en cualquier parte de su nombre.

Solución: En este ejemplo

1. Product Saree, Nail Paint, Jeans y Lehenga contienen el carácter ‘a’ en ellos.
2. Los precios medios de estos productos serán

= (1200+ 100+ 1000+ 5000)/4 = 7300/4 = 1825 

Entonces, la fórmula completa para calcular el promedio es la siguiente

AVERAGEIFS(C2:C11,A2:A11,"*a")

Conclusión

Usando PROMEDIO, es posible encontrar el promedio sin ningún criterio. Usando PROMEDIO.SI, el promedio se puede calcular con criterios particulares y usando PROMEDIO.SI, el promedio se puede calcular con varios criterios.