En Google Sheets no existe AGREGAR, la función que en Excel promedia saltando errores. Para promediar un rango que contiene #DIV/0! o #N/A usa =PROMEDIO(FILTER(B2:B,ESNUMERO(B2:B))): FILTER deja pasar solo las celdas con número. Para ignorar los ceros sirve lo mismo que en Excel, =PROMEDIO.SI(B2:B,"<>0"), y para ignorar ceros y errores a la vez, =PROMEDIO(FILTER(B2:B,SI.ERROR(B2:B<>0,FALSO))). Recuerda que en Sheets los errores se muestran con su nombre en inglés.
Sintaxis
| Necesitas | Fórmula en Sheets | En Excel |
|---|---|---|
| Ignorar errores | =PROMEDIO(FILTER(B2:B,ESNUMERO(B2:B))) | =AGREGAR(1,6,B2:B20) |
| Ignorar errores (otra forma) | =PROMEDIO(ARRAYFORMULA(SI.ERROR(B2:B,""))) | =PROMEDIO(SI.ERROR(B2:B20,"")) |
| Ignorar ceros | =PROMEDIO.SI(B2:B,"<>0") | Igual |
| Ignorar ceros y errores | =PROMEDIO(FILTER(B2:B,SI.ERROR(B2:B<>0,FALSO))) | =PROMEDIO(FILTRAR(B2:B20,SI.ERROR(B2:B20<>0,FALSO))) |
| Ceros y errores, con una condición | =PROMEDIO(FILTER(C2:C,A2:A="Norte",SI.ERROR(C2:C<>0,FALSO))) | Con FILTRAR y * |
En la segunda fórmula, ARRAYFORMULA hace que SI.ERROR se aplique a cada celda del rango; sin ella, SI.ERROR solo evaluaría una celda. FILTER no lo necesita, porque ya trabaja con columnas completas.
Paso a paso
- Localiza los errores: en Sheets se ven como #DIV/0!, #N/A, #VALUE! o #REF!, con un triángulo rojo; al pasar el cursor, el mensaje explica la causa.
- Decide si el error es un dato que falta (puede quedar fuera) o un fallo que hay que corregir.
- Escribe la fórmula de FILTER con ESNUMERO en una celda fuera de la columna.
- Junto al resultado, cuenta cuántos valores entraron:
=CONTAR(B2:B)cuenta solo números, así que ya excluye errores. - Si también hay ceros que no son datos, cambia la condición por
SI.ERROR(B2:B<>0,FALSO).
Ejemplos resueltos
Ejemplo 1: precio por unidad con divisiones fallidas
La columna D calcula =B2/C2 (importe entre unidades) y algunas filas fallan: los precios por unidad son 4.5, #DIV/0! (sin unidades), 5.2, 4.8, #N/A (importe traído con BUSCARV que no encontró la clave) y 5.5.
=PROMEDIO(D2:D7) → #DIV/0!, el primer error del rango
=PROMEDIO(FILTER(D2:D7,ESNUMERO(D2:D7))) → 20 / 4 = 5
Quedan fuera las dos filas con error. El #N/A, en cambio, merece revisión: puede ser una clave mal escrita, y entonces el producto sí existe y falta en el promedio.
Ejemplo 2: ceros y errores en la misma columna
Ahora la columna tiene 4.5, 0, #DIV/0!, 5.2, 0 y 4.8, donde los ceros son productos sin precio cargado.
=PROMEDIO(FILTER(D2:D7,SI.ERROR(D2:D7<>0,FALSO))) → 14.5 / 3 = 4.83
Para cada celda, D2:D7<>0 da VERDADERO si es un número distinto de cero; en una celda con error, la comparación da error y SI.ERROR lo convierte en FALSO. Solo pasan 4.5, 5.2 y 4.8. Con PROMEDIO.SI(D2:D7,"<>0") no basta: el error del rango a promediar sigue apareciendo.
Ejemplo 3: arreglar el origen sin crear ceros
En lugar de filtrar después, puedes evitar el error en la columna D: =SI.ERROR(B2/C2,""). Las filas que fallan quedan en blanco y =PROMEDIO(D2:D) las ignora.
Lo que no debes hacer es =SI.ERROR(B2/C2,0): con los datos del ejemplo 1, el promedio pasaría de 5 a 20 / 6 = 3.33, porque los dos errores entrarían como precios de cero. El razonamiento es el mismo que en Excel, explicado en promedio ignorando errores en Excel.
Errores comunes
- Buscar AGREGAR o AGGREGATE. No existe en Google Sheets. Si abres en Sheets un archivo de Excel que la usa, la celda mostrará un error.
- SI.ERROR sin ARRAYFORMULA sobre un rango.
=PROMEDIO(SI.ERROR(B2:B,""))evalúa solo la primera celda. Usa ARRAYFORMULA o la versión con FILTER. - FILTER sin coincidencias. Si todas las celdas son errores, FILTER devuelve #N/A.
- Usar
">0"con datos negativos. Quita también los negativos; para conservarlos, usa"<>0", como se explica en promedio sin ceros en Excel. - Ocultar errores que son pistas. Un #REF! suele indicar que se borró una columna de la que dependía la fórmula; corrígelo en lugar de filtrarlo.
Para revisar un costo promedio una vez limpios los datos, usa la calculadora de costo promedio. Si además tienes filas ocultas por un filtro, mira promedio filtrado en Google Sheets, porque SUBTOTALES no salta errores.