Para sacar el promedio de un rango que contiene errores como #N/D o #¡DIV/0!, usa =AGREGAR(1,6,B2:B10). El primer argumento (1) le dice a AGREGAR que calcule un promedio y el segundo (6), que omita los valores de error. PROMEDIO, en cambio, devuelve el primer error que encuentra. Antes de esconder errores, eso sí, averigua de dónde vienen: a veces el error es la única pista de que un dato está mal.
Sintaxis
=AGREGAR(núm_función, opciones, ref1, [ref2], …)
| Argumento | Para promediar | Otros valores útiles |
|---|---|---|
núm_función | 1 = promedio | 12 = mediana, 14 = k-ésimo mayor |
opciones | 6 = omitir errores | 5 = omitir filas ocultas, 7 = omitir filas ocultas y errores |
ref1 | El rango | Hasta 253 referencias |
Alternativas según tu versión de Excel:
| Fórmula | Funciona en |
|---|---|
=AGREGAR(1,6,B2:B10) | Excel 2010 y posteriores |
=PROMEDIO(SI.ERROR(B2:B10,"")) | Excel 365 y 2021 con Enter; en versiones anteriores con Ctrl + Mayús + Enter |
=PROMEDIO(FILTRAR(B2:B10,ESNUMERO(B2:B10))) | Excel 365 y 2021 |
Paso a paso
- Localiza los errores: Inicio > Buscar y seleccionar > Ir a especial > Fórmulas, marca solo Errores y acepta. Excel selecciona todas las celdas con error.
- Decide si cada error es un dato que falta (puede quedar fuera del promedio) o un problema por corregir (una clave mal escrita, una división mal planteada).
- Escribe
=AGREGAR(en la celda del resultado; Excel muestra una lista de funciones: elige1 - PROMEDIO. - Escribe el separador y elige
6 - Omitir valores de error. - Escribe el separador, selecciona el rango, cierra el paréntesis y pulsa Enter.
- Anota junto al resultado cuántos valores se excluyeron:
=SUMAPRODUCTO(--ESERROR(B2:B10)).
Ejemplos resueltos
Ejemplo 1: rendimiento de combustible con divisiones entre cero
Una flotilla registra kilómetros (B) y litros (C) por vehículo, y la columna D calcula =B2/C2. Dos vehículos no salieron en la semana (0 km, 0 litros) y muestran #¡DIV/0!. Los demás rinden 12.5, 14.2, 11.8 y 13.1 km/l.
=PROMEDIO(D2:D7) → #¡DIV/0!
=AGREGAR(1,6,D2:D7) → 51.6 / 4 = 12.9 km/l
Los vehículos detenidos quedan fuera del promedio, que es lo correcto: no tienen rendimiento, no es que rindan cero. Puedes comprobar el cálculo con la calculadora de consumo promedio de combustible.
Ejemplo 2: corregir en el origen, sin convertir el error en cero
Con los mismos datos, otra persona cambió la columna D a =SI.ERROR(B2/C2,0) para «quitar los errores». Ahora =PROMEDIO(D2:D7) da 51.6 / 6 = 8.6 km/l: los dos vehículos detenidos entraron como si rindieran cero.
La versión correcta en el origen devuelve texto vacío, que PROMEDIO ignora:
=SI.ERROR(B2/C2,"")
Y aún mejor, con la condición explícita, que no esconde otros errores inesperados: =SI(C2=0,"",B2/C2). Con cualquiera de las dos, =PROMEDIO(D2:D7) vuelve a dar 12.9.
Ejemplo 3: ignorar errores y filtrar por una condición
La columna A tiene la región de cada vehículo: Norte, Sur, Norte, Norte, Sur, Norte; la D, los rendimientos 12.5, #¡DIV/0!, 14.2, #¡DIV/0!, 11.8 y 13.1. AGREGAR no acepta criterios, y PROMEDIO.SI.CONJUNTO no resuelve los errores del rango a promediar de forma confiable. En Excel 365:
=PROMEDIO(FILTRAR(D2:D7,(A2:A7="Norte")*ESNUMERO(D2:D7)))
El producto de las dos condiciones vale 1 solo cuando la fila es del Norte y tiene número. Entran 12.5, 14.2 y 13.1: el promedio es 13.27 km/l. En versiones sin FILTRAR, usa =PROMEDIO(SI((A2:A7="Norte")*ESNUMERO(D2:D7),D2:D7)) confirmada con Ctrl + Mayús + Enter.
Errores comunes
- Convertir errores en ceros.
SI.ERROR(…,0)hace desaparecer el aviso y baja el promedio. Devuelve""o deja que AGREGAR los omita. - Usar la opción 2 o 3 sin saber qué hacen. La opción 3 también omite filas ocultas; si filtraste la tabla, cambia el resultado. Para errores únicamente, usa 6.
- Esconder un error que había que corregir. Un #N/D de BUSCARV suele ser una clave mal escrita; si lo omites, el promedio deja fuera un producto que sí existe. Revisa BUSCARV y promedios.
- Usar AGREGAR con una matriz calculada. Con las funciones 1 a 13, AGREGAR solo acepta referencias a rangos, no expresiones como
B2:B10*2. - Pensar que AGREGAR existe en Google Sheets. No existe; mira ignorar ceros y errores en Google Sheets.
Si el problema no son errores sino filas ocultas o filtradas, la página de promedio de celdas visibles explica las opciones 5 y 7 de AGREGAR y la función SUBTOTALES. Y si el promedio sale distinto sin mostrar ningún error, revisa la tabla de errores comunes del promedio.
Preguntas frecuentes
¿Por qué PROMEDIO no ignora los errores como ignora el texto?
Porque un error indica que un cálculo anterior falló, y Excel lo propaga a propósito para que no pase desapercibido. Para saltarlo tienes que pedirlo de forma explícita, con AGREGAR o con una fórmula de matriz.
¿AGREGAR funciona en Google Sheets?
No. Hojas de cálculo de Google no tiene AGREGAR; allí se usa PROMEDIO con FILTER e ESNUMERO o SI.ERROR.