Operaciones con rangos que contienen errores

Si una fórmula en nuestra hoja de Excel se refiere a un rango que contiene errores, como #N/A, el resultado será también error.
Si el rango contiene sólo algunas celdas, una solución es, sencillamente, borrar el contenido de las celdas con error.
Pero hay situaciones en las cuales no queremos borrar el contenido de las celdas ya sea porque se trata de una gran cantidad de datos o por cualquier otro motivo.
En esos casos necesitamos crear fórmulas que ignoren las celdas con un valor de error en el rango.
En esta nota veremos como sumar, contar e imitar la función SUMAPRODUCTO en estas situaciones.

Empezamos por considerar un rango en el cual hay vatios tipos de errores (de hecho, todos los errores que se pueden dar en Excel)




Si usamos la fórmula =SUMA(A2:A11), el resultado será #¡VALOR!.

Para superar este inconveniente usamos esta fórmula matricial

={SUMA(SI(ESERROR(A2:A11),0,A2:A11))}

Como siempre recordamos que las fórmulas matriciales son introducidas en las celdas pulsando simultáneamente Ctrl+Mayúsculas+Enter.

Esta fórmula crea un vector donde si un valor del rango da resultado error al ser evaluado por la función ESERROR, este valor es convertido a 0. En caso contrario se mantiene el valor original. La función SUMA realiza la operación con los valores del vector creado por la función SI.

Si queremos contar cuantos valores que no son error hay en el rango usamos esta fórmula matricial

={SUMA((NO(ESERROR(A2:A11)))*1)}

Esta misma fórmula puede escribirse de esta forma más "académica"

={SUMA(--NO(ESERROR(A2:A11)))}

También podemos usar funciones base de datos para resolver este problema.
Si el tipo de error es el mismo en todas las celdas erróneas del rango, por ejemplo #N/A , como en esta situación



definimos el área de criterios en el rango C1:C2 como mostramos en la imagen arriba y creamos la fórmula

=BDSUMA(A1:A11,A1,C1:C2)

Para contar cuantos valores no erróneos hay en la lista usamos esta otra fórmula

=BDCONTAR(A1:A11,A1,C1:C2)

Si tenemos distintos tipos de valores en la lista, en lugar de crear varios criterios, podemos usar un único criterio, como se muestra aquí



Hay que prestar atención que el área de criterios sigue siendo el rango C1:C2, pero hemos dejado la celda C1 vacía y en la celda C2 tenemos una fórmula y no un valor. Las fórmulas para sumar y contar son idénticas a las del ejemplo anterior.



Finalmente tenemos que enfrentarnos con el caso que el rango con errores aparezca en una función SUMAPRODUCTO. En este caso tenemos que desistir de usar esta función e imitarla con una fórmula matricial.
Si queremos obtener la suma de los productos de los miembros de las listas 1 y 2 como en esta imagen



tendremos que usar esta fórmula matricial

={SUMA(SI(ESERROR(A2:A11*B2:B11),0,(A2:A11*B2:B11)))}

Si tenemos más de dos vectores, es conveniente usar nombres para señalar los rangos. Por ejemplo, en este caso creamos tres nombres Lista1, Lista2 y Lista3



El cuaderno con los ejemplos se puede descargar aquí

Technorati Tags: MS Excel