Excel no tiene un «PROMEDIO.SI con O»: PROMEDIO.SI.CONJUNTO solo promedia las filas que cumplen todas las condiciones. Para promediar las filas que cumplen una condición u otra, en Excel 365 usa =PROMEDIO(FILTRAR(C2:C9,(B2:B9="Tienda")+(B2:B9="En línea"))): el signo + funciona como O. En cualquier versión, suma y cuenta por separado: =(SUMAR.SI(B2:B9,"Tienda",C2:C9)+SUMAR.SI(B2:B9,"En línea",C2:C9))/(CONTAR.SI(B2:B9,"Tienda")+CONTAR.SI(B2:B9,"En línea")).
Sintaxis según el caso
| Caso | Fórmula |
|---|---|
| Dos valores en la misma columna (Excel 365) | =PROMEDIO(FILTRAR(C2:C9,(B2:B9="Tienda")+(B2:B9="En línea"))) |
| Dos valores en la misma columna (cualquier versión) | =(SUMAR.SI(B2:B9,"Tienda",C2:C9)+SUMAR.SI(B2:B9,"En línea",C2:C9))/(CONTAR.SI(B2:B9,"Tienda")+CONTAR.SI(B2:B9,"En línea")) |
| Condiciones en columnas distintas | =SUMAPRODUCTO(((A2:A9="Ana")+(B2:B9="Mayoreo")>0)*C2:C9)/SUMAPRODUCTO(--((A2:A9="Ana")+(B2:B9="Mayoreo")>0)) |
| Fuera de un intervalo (menor que o mayor que) | =(SUMAR.SI(C2:C9,"<700")+SUMAR.SI(C2:C9,">2000"))/(CONTAR.SI(C2:C9,"<700")+CONTAR.SI(C2:C9,">2000")) |
En las fórmulas de matriz, cada comparación entre paréntesis produce VERDADERO o FALSO; al sumarlas, Excel las convierte en 1 y 0. Un resultado mayor que 0 significa «cumple al menos una».
Paso a paso con FILTRAR
- Escribe cada condición como una comparación entre paréntesis:
(B2:B9="Tienda"). - Une las condiciones O con
+y las condiciones Y con*. - Pon esa expresión como segundo argumento de
FILTRAR, con el rango a promediar como primero. - Envuelve todo en
PROMEDIO(…)y pulsa Enter. - Si ninguna fila cumple, FILTRAR devuelve #CALC! porque no puede devolver una lista vacía, y PROMEDIO hereda el error. Para mostrar un mensaje, envuelve todo:
=SI.ERROR(PROMEDIO(FILTRAR(…)),"Sin ventas").
Los ejemplos usan esta tabla de ventas (A1:C9):
| Vendedor | Canal | Monto |
|---|---|---|
| Ana | Tienda | 850 |
| Luis | En línea | 1,200 |
| Marta | Tienda | 640 |
| Ana | En línea | 990 |
| Ana | Mayoreo | 2,300 |
| Luis | Tienda | 720 |
| Marta | Mayoreo | 1,850 |
| Pedro | En línea | 1,100 |
Ejemplos resueltos
Ejemplo 1: dos canales de la misma columna
Monto promedio de las ventas de Tienda o En línea (es decir, sin Mayoreo):
=PROMEDIO(FILTRAR(C2:C9,(B2:B9="Tienda")+(B2:B9="En línea")))
Entran 850, 1,200, 640, 990, 720 y 1,100. Suman 5,500 entre 6 filas: 916.67. Con la versión de SUMAR.SI y CONTAR.SI obtienes lo mismo: (2,210 + 3,290) / (3 + 3). En este caso concreto también sirve =PROMEDIO.SI(B2:B9,"<>Mayoreo",C2:C9), porque «Tienda o En línea» es lo mismo que «todo menos Mayoreo».
Ejemplo 2: condiciones en columnas distintas, sin contar doble
Monto promedio de las ventas de Ana o del canal Mayoreo. La venta de Ana en Mayoreo (2,300) cumple las dos condiciones.
Si sumas las condiciones sin más, esa fila vale 2 y entra dos veces: (850 + 990 + 2 × 2,300 + 1,850) / 5 = 1,658, que es incorrecto. Por eso se compara con >0:
=SUMAPRODUCTO(((A2:A9="Ana")+(B2:B9="Mayoreo")>0)*C2:C9)/SUMAPRODUCTO(--((A2:A9="Ana")+(B2:B9="Mayoreo")>0))
Entran una sola vez 850, 990, 2,300 y 1,850: 5,990 / 4 = 1,497.5. Con FILTRAR no hace falta el >0, porque FILTRAR toma cualquier valor distinto de cero como verdadero: =PROMEDIO(FILTRAR(C2:C9,(A2:A9="Ana")+(B2:B9="Mayoreo"))).
Ejemplo 3: valores fuera de un intervalo
Quieres el promedio de las ventas atípicas: menores que 700 o mayores que 2,000.
=(SUMAR.SI(C2:C9,"<700")+SUMAR.SI(C2:C9,">2000"))/(CONTAR.SI(C2:C9,"<700")+CONTAR.SI(C2:C9,">2000"))
Cumplen 640 y 2,300: el promedio es 1,470. Como un número no puede ser a la vez menor que 700 y mayor que 2,000, aquí no hay riesgo de contar doble y la suma de dos SUMAR.SI es segura. Para el caso contrario (entre 700 y 2,000, es decir, condición Y), basta con PROMEDIO.SI.CONJUNTO.
Errores comunes
- Poner las dos opciones en PROMEDIO.SI.CONJUNTO.
=PROMEDIO.SI.CONJUNTO(C2:C9,B2:B9,"Tienda",B2:B9,"En línea")busca filas que sean Tienda y En línea a la vez: no hay ninguna y devuelve #¡DIV/0!. - Promediar los dos promedios.
=PROMEDIO(PROMEDIO.SI(…"Tienda"…),PROMEDIO.SI(…"En línea"…))da el mismo peso a cada canal aunque uno tenga más ventas. Suma los importes y divide entre el total de filas. - Contar doble con condiciones de columnas distintas. Usa
>0en SUMAPRODUCTO, o FILTRAR. - Usar
*en lugar de+. El asterisco equivale a Y.(A2:A9="Ana")*(B2:B9="Mayoreo")solo deja la venta de Ana en Mayoreo. - Texto con espacios o acentos distintos. «En linea» sin acento no es igual a «En línea». Revisa la columna con una tabla dinámica, que muestra cada variante como una fila.
Si te interesa el ticket promedio de un negocio en general, la calculadora de ticket promedio hace el cálculo con tus totales. En Google Sheets la técnica con + funciona igual dentro de FILTER: lo vemos en FILTER en Google Sheets.